echodarius
asked on
Excel: Calculating Percentage Averages
Okay.
So I have a nice simple spreadsheet, and I want to calculate the averages of several derived percentage values. However, in some cases the percentage is over 100%.
What I want to do is calculate the real average - discarding any value over 100%. For (very simple) example:
The "average" of these three values is:
(120%+80%+100%)/3 = 100%
The *real* average of these values should be:
(120%+80%+100%)/3 = 93.33%
There must be a function in Excel to do this, and I'm reasonably certain it's easy. I'm just not finding it in the help dox, most likely because I'm not sure of the name of the action I'm looking for.
So I have a nice simple spreadsheet, and I want to calculate the averages of several derived percentage values. However, in some cases the percentage is over 100%.
What I want to do is calculate the real average - discarding any value over 100%. For (very simple) example:
The "average" of these three values is:
(120%+80%+100%)/3 = 100%
The *real* average of these values should be:
(120%+80%+100%)/3 = 93.33%
There must be a function in Excel to do this, and I'm reasonably certain it's easy. I'm just not finding it in the help dox, most likely because I'm not sure of the name of the action I'm looking for.
Monchanger, asker wants to shave the above 100% to 100% and take the avg from that
:O)Bruintje
:O)Bruintje
echodarius
You could use two different functions E.g.
One to give the *real* value and the other to gain the average.
Values in cal A:
120
80
100
set B1 equal to =IF(A1>100,100,A1)
What you can do then is Hid the B Column so that the vaules can not be seen (If you want this) and then run the average function of your new vaules
e.g. =Average(B1:B3)
Hope this helps
Ed.
You could use two different functions E.g.
One to give the *real* value and the other to gain the average.
Values in cal A:
120
80
100
set B1 equal to =IF(A1>100,100,A1)
What you can do then is Hid the B Column so that the vaules can not be seen (If you want this) and then run the average function of your new vaules
e.g. =Average(B1:B3)
Hope this helps
Ed.
I had another look at this and the only other way I can come up with is to write a small macro to attain the correct value.
If you want a macro instead I will do this.
Ed.
If you want a macro instead I will do this.
Ed.
Hi echodarius,
if you enter this formula instead of the one u use now
=AVERAGE(IF(A3:A5>1,1,A3:A 5))
then in the formula bar do it with CTRL+SHIFT+ENTER so it will become an array formula then you'll get the 93% you're looking for
:O)Bruintje
if you enter this formula instead of the one u use now
=AVERAGE(IF(A3:A5>1,1,A3:A
then in the formula bar do it with CTRL+SHIFT+ENTER so it will become an array formula then you'll get the 93% you're looking for
:O)Bruintje
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
bruintje,
thanx -- that's exactly what i was looking for, actually. the extra stuff on array formulae is also useful -- i'll run through that when I have the time.
i had been doing this with a hidden column that i just manually input "100%" in if the value in the other formula was > 100%. . . but this automates it, which is what the point of the whole spreadsheet thing is.
xtra points for the helpful weblinks. ;)
regards,
-- d
thanx -- that's exactly what i was looking for, actually. the extra stuff on array formulae is also useful -- i'll run through that when I have the time.
i had been doing this with a hidden column that i just manually input "100%" in if the value in the other formula was > 100%. . . but this automates it, which is what the point of the whole spreadsheet thing is.
xtra points for the helpful weblinks. ;)
regards,
-- d
thanks for the grade and points, i'm using this stuff on these formulas a lot more at work but i learned it here at EE
I don't think I've heard about what you refer to as a "real average" (I couldn't find that term on Google...) - could you explain how that works ? (I just can't figure out how to get 93.33% out of 100% and 80% - did you accidentally use 280/3 instead of 180/3 ?)