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

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

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!