Using the “average” function in Excel 4

[wec43]

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.

 

[SkipVought]

Hi,

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


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

What if the max occurs more than once?

 

[SkipVought]

Good point, mint!
[tt]
=SUMPRODUCT((list<MAX(list))*(list))/(COUNT(list)-COUNTIF(list,MAX(list)))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wec43]

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 !

 

[SkipVought]

[tt]
=SUMPRODUCT((list[highlight #FCE94F]>[/highlight]MIN(list))*(list))/(COUNT(list)-COUNTIF(list,MIN(list)))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wec43]

Thanks again Skip !

 

[combo]

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

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]