asked on

Creating an overall average of individual Averages

see attached example.
I would like to accomplish what I show via adding other rows to do the intermediatry calcs in multiple steps in a single formula
i.e.
I have a row of numbers, and would like to divide each cell in that row (giving a ratio for each cell in the row)- and then averaging all of these ratios to get an overall ratio

The data given are in Cols A:C
I'm trying to produce cells G12 and H12 via cell formula
without the need of creating cells G4:H8

Using MS Excel 07
thanks
OverallLossAvg-Example.xlsx

NBVC

Try:

=AVERAGE(B4:B8/B2)

confirmed with CTRL+SHIFT+ENTER and copied to next cell

worm-getter

for 2012:

=AVERAGE(B4/B$2, B5/B$2, B6/B$2, B7/B$2, B8/B$2)

you can do 2013 yourself.

NBVC

or

=AVERAGE(INDEX(B4:B8/B2,0))

confirmed with just ENTER

endurance

ASKER

Thanks - the AVERAGE(INDEX(B4:B8/B2,0)) and
=AVERAGE(B4:B8/B2)
are just what I was looking for.
And I was able to extend it to a different application so that it multiplied each value in a row is divided by a value in a different row - and the cell showed the overall average of all of these ratios - AVERAGE(INDEX(B4:B8/C4:C8,0))

Though one problem I just encountered when doing so - if a pair of cells (numerator and denominator) were zero (e.g. no claims) - it gave me a #Div0 error - is there a way to adjust the above formulas to account for zero - so anything ratio with a zero demonitor doesn't count towards the average?

worm-getter

it should only give a #Div0 error when the denominator = 0.
try using a checking condition.

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

endurance

ASKER

Yep!! that did it,
one last Q - is there any way to adjust the
AVERAGE(INDEX(B4:B8/B2,0))
formula to account for zeros
?

worm-getter

use if statement

endurance

ASKER

What would be the formula
I tried
=AVERAGE(INDEX(IF($E$4:$E$8<>0,$B$4:$B$8/$E$4:$E$8),0))

but got an error

worm-getter

try moving the last 0.

NBVC

Wouldn't it be like I showed before?

=AVERAGE(IF($E$4:$E$8<>0,B$4:$B$8/$E$4:$E$8))

you would need to confirm with CTRL+SHIFT+ENTER.

endurance

ASKER

yep that was great, was just wondering if you can do it with the Average(Index ...) way
(preferred to only have to press ENTER )

NBVC

Because the #Div/0 becomes an element in the resulting array, we need the Array formula to "cancel" out the error, so need the CSE confirmation.

endurance

ASKER

k, thanks

endurance

ASKER

How would I modify

=AVERAGE(IF(C4:C8<>0,B4:B8/C4:C8))
if I want to exclude all ratios where EITHER the denominator is 0 OR the numerator is blank?