Throw out the high and low

[grumpus]

I know how to calculate the average of a range of numbers but what I want to do is to take that range but exclude the "high" (MAX) and "low" (MIN) from the calculation. Is there a formula that can do that without having to use the MAX and MIN formulas to identify those values and then manually deleting them from the data?


Column A is an example of the data with the average calculated in A16. What I'm looking for is the answer in C16 with one formula if possible.


http://speedy.sh/wB8Hf/Book3.xlsx


Thanks in advance for taking on this challenge.


Grumpus

 

[Jai9]

Hi,


I cant download your file due to work restrictions.


I tried with some fictitious data in F11:F20.


Try,


=(SUM(F11:F20)-MAX(F11:F20)-MIN(F11:F20))/8


Jai

 

[Trader273]

Hopefully this helps:


https://www.dropbox.com/s/up85yf0z01o8tqx/Average%20without%20Outliers.xlsx

 

[grumpus]

Yes, that nearly got me there. I tweaked the denominator slightly to account for a varied range.


=(SUM(A1:A15)-MAX(A1:A15)-MIN(A1:A15))/(COUNT(A1:A15)-2)


Thanks your time!


Grumpus

 

[grumpus]

Thank you too Nick M for the tip with LARGE and SMALL.

Happy Holidays!