Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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.

Check out our Excel Resources

Re: Array (score card)

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<=LARGE(A1:A5,2)),A1:A5))

Celia

Re: Array (score card)

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.

Re: Array (score card)

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.