Improve company productivity with a Business Account.Sign Up

x
?
Solved

Creating an overall average of individual Averages

Posted on 2013-05-22
15
Medium Priority
?
285 Views
Last Modified: 2013-05-24
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
0
Comment
Question by:endurance
  • 6
  • 5
  • 4
15 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39187545
Try:

=AVERAGE(B4:B8/B2)

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

Expert Comment

by:worm-getter
ID: 39187546
for 2012:

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

you can do 2013 yourself.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39187548
or

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

confirmed with just ENTER
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 

Author Comment

by:endurance
ID: 39187777
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
 
LVL 6

Expert Comment

by:worm-getter
ID: 39187846
it should only give a #Div0 error when the denominator = 0.
try using a checking condition.
0
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39187877
Try:

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

confirmed with CTRL+SHIFT+ENTER
0
 

Author Comment

by:endurance
ID: 39188272
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
 
LVL 6

Expert Comment

by:worm-getter
ID: 39188325
use if statement
0
 

Author Comment

by:endurance
ID: 39188335
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
 
LVL 6

Expert Comment

by:worm-getter
ID: 39188407
try moving the last 0.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39188447
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 Comment

by:endurance
ID: 39188476
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39188572
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 Comment

by:endurance
ID: 39191102
k, thanks
0
 

Author Comment

by:endurance
ID: 39194403
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Here is why.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question