Array (score card)

Posted by Norm on February 25, 2000 4:01 PM

I have five numbers in a score card.
65.5 63 57.5 57 56 (answer)
68.5 66 71.5 65 64 (answer)
I need to throw out the high and the low
and average the remaining three?

The problem is the high and low aren't
always in the same cell.

Any help would be appreciated.

Posted by Celia on February 25, 2000 5:37 PM

Norm
If your scores are in cells A1:A5, the following array formula will give you the required average

=AVERAGE(IF((A1:A5>=SMALL(A1:A5,2))*(A1:A5&LT;=LARGE(A1:A5,2)),A1:A5))

Celia

Posted by Celia on February 25, 2000 10:44 PM

PS. The above formula only works if there are no equal lows and no equal highs in A1:A5.

Posted by Celia on February 26, 2000 1:19 AM

Norm

I found the following formula at the MS Knowledge Base (Article: Q108280)

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

Celia