Solved

Excel: Calculating Percentage Averages

Posted on 2002-05-30
8
21,865 Views
Last Modified: 2007-11-27
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.
0
Comment
Question by:echodarius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:Monchanger
ID: 7044647
I've been over the list of Excel (2000) functions a whole bunch of times and never saw something like that. I'm sorry, but you might have to  do the math yourself.

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 ?)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7044679
Monchanger, asker wants to shave the above 100% to 100% and take the avg from that

:O)Bruintje
0
 
LVL 3

Expert Comment

by:Elmo_
ID: 7044738
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 3

Expert Comment

by:Elmo_
ID: 7044753
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.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7044806
Hi echodarius,

if you enter this formula instead of the one u use now

=AVERAGE(IF(A3:A5>1,1,A3:A5))

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
0
 
LVL 44

Accepted Solution

by:
bruintje earned 250 total points
ID: 7044826
a bit more on array formulas

http://www.winplanet.com/winplanet/tips/4045/1/

HTH:O)Bruintje
0
 
LVL 1

Author Comment

by:echodarius
ID: 7044902
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
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7044918
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
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

626 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