Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using the “average” function in Excel 4

Status
Not open for further replies.

wec43

Technical User
Jul 27, 2003
3
US
Trying to create a formula that will give me an average number from a list of numbers in excel, but I want the formula to exclude the highest number within the data range before giving me the average.

Note - the highest number could be anywhere within the data range.




 
Hi,

How about this...
[tt]
=SUMPRODUCT((list<MAX(list))*(list))/(COUNT(list)-1)
[/tt]
...where list is your range.


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Good point, mint!
[tt]
=SUMPRODUCT((list<MAX(list))*(list))/(COUNT(list)-COUNTIF(list,MAX(list)))
[/tt]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Skip - your solution worked PERFECTLY !!!!!! Thanks

follow-up question - if trying to exclude the lowest (min) number ....I changed the "max" in the formula to "min" but did not get the correct answer. What must I do in order to modify your formula to exclude the lowest number within the data range?

Your formula - =SUMPRODUCT((list<MAX(list))*(list))/(COUNT(list)-COUNTIF(list,MAX(list)))
Modified formula -

Mint - thanks for your follow-up also !
 
[tt]
=SUMPRODUCT((list[highlight #FCE94F]>[/highlight]MIN(list))*(list))/(COUNT(list)-COUNTIF(list,MIN(list)))
[/tt]

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
A little simpler and direct:
[tt]=AVERAGEIFS(list,list,"<"&MAX(list))[/tt]
Multiple conditions can be easily added in [tt]AVERAGEIFS[/tt] arguments list.

combo
 
I agree that combo's AVERAGEIFS() solution is more concise. It deserves a STAR!

I've just never gotten accustomed to the aggregationIFS() argument set. It is not intuitive to me. Each time I've tried to use, I stumble around to get it to work. The OPERATORS (other than EQUALS, which does not need an operator) are awkward.

SUMPRODUCT() is so much more intuitive. The OPERATORS ("<" ">") don't need to be in QUOTES. The expressions are able to be evaluated in the FORMULA BAR.

I guess I'm just an old curmudgeon!

Anyhow, here's a list of three formulas:

Throw out MIN values
=SUMPRODUCT((list>MIN(list))*(list))/SUMPRODUCT(--(list>MIN(list)))

=AVERAGEIFS(list,list,">"MIN(list))


Throw out MAX values
=SUMPRODUCT((list<MAX(list))*(list))/SUMPRODUCT(--(list<MAX(list)))

=AVERAGEIFS(list,list,"<"&MAX(list))


Throw out MIN & MAX values
=SUMPRODUCT((list<MAX(list))*(list>MIN(list))*(list))
/SUMPRODUCT(--(list<MAX(list)*(list>MIN(list))))

=AVERAGEIFS(list,list,"<"&MAX(list),list,">"&MIN(list))


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top