Olympic Averages
Posted by Mike on September 26, 2001 11:07 AM
I have a list of nine ratings each for a number of applicants. I want Excel to scan and toss the highest rating, and the lowest, and then give me an average of the remaining seven. Is this possible?
Posted by Barrie Davidson on September 26, 2001 11:16 AM
Try
=SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9)/(COUNT(A1:A9)-2)
Regards,
BarrieBarrie Davidson
Posted by Mike on September 26, 2001 11:29 AM
Thank you for the idea, but no go -- it returned 35.86 instead of 7.17. The other problem is if I have more then one high score, and more then one low, like 7, 8, 7, 9, 6, NA, 7, 6, 9. I only want it to throw one 9 and one 6 before it averages.
Posted by Mike on September 26, 2001 11:38 AM
Thank you for the idea, but no go -- it returned 35.86 instead of 7.17. The other problem is if I have more then one high score, and more then one low, like 7, 8, 7, 9, 6, NA, 7, 6, 9. I only want it to throw one 9 and one 6 before it averages.
Posted by Barrie Davidson on September 26, 2001 11:39 AM
Time for me to wake up!!
Try this (missed my parentheses)
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2)
Regards,
BarrieBarrie Davidson
Posted by IML on September 26, 2001 11:39 AM
Outright theft
How about
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2)
this returns 7.33. Your 7.17 answer would be if you excluded a 7, not the 6 in your example.
Good luck and thanks to Barrie.
Posted by Juan Pablo on September 26, 2001 11:41 AM
Mike,
Try the formula that Barrie gave you but he's lacking a couple of parenthesis...
=( SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9) )/(COUNT(A1:A9)-2)
And regarding your question, this formula should erase only ONE of the Max, and one of the MIN...
Juan Pablo
-----------------------
Posted by Mike on September 26, 2001 11:42 AM
Thank you for the idea, but no go -- it returned 35.86 instead of 7.17. The other problem is if I have more then one high score, and more then one low, like 7, 8, 7, 9, 6, NA, 7, 6, 9. I only want it to throw one 9 and one 6 before it averages.
Posted by Russell Hauf on September 26, 2001 11:43 AM
Just add some parentheses:
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2)
This should do the trick.
HTH,
Russell
Posted by Mike on September 26, 2001 1:21 PM
Thank you -- works beautifully!
Posted by Mike on September 26, 2001 1:23 PM
the formula that Barrie gave you but he's lacking a couple of parenthesis...
Thank you!
Posted by Mike on September 26, 2001 1:25 PM
Re: Time for me to wake up!!
this (missed my parentheses) =(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2)
That does it! Thank you!
