# 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
###### Who is Participating?

Commented:
Try:

=AVERAGE(IF(C4:C8<>0,B4:B8/C4:C8))

confirmed with CTRL+SHIFT+ENTER
0

Commented:
Try:

=AVERAGE(B4:B8/B2)

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

Commented:
for 2012:

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

you can do 2013 yourself.
0

Commented:
or

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

confirmed with just ENTER
0

Author Commented:
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?
0

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

Author Commented:
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
?
0

Commented:
use if statement
0

Author Commented:
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
0

Commented:
try moving the last 0.
0

Commented:
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.
0

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

Commented:
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.
0

Author Commented:
k, thanks
0

Author Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.