Macro

Posted by Roger Pinke on November 01, 2000 5:11 AM

I need to calcualte an average for test scores while dropping the lowest score. Any ideas

Posted by Tim Francis-Wright on November 01, 2000 7:24 AM

You could set up a macro to figure out which is
the lowest in a set of grades, subtract the
outlier from the sum of all the grades, then
divide by the number of grades, minus one.

But there's a way to do this without a macro.
If the test scores are in a range called grades,
the following will calculate the average with
the lowest score dropped:
(It's an array formula, so use Control-Shift-Enter instead of Enter when inputting it.)

=AVERAGE(LARGE(grades,ROW(INDIRECT("1:"&COUNT(grades)-1))))

The row(indirect(... phrase makes an array starting
at 1 and ending with the number of grades minus 1.
The formula then averages the grades that
the LARGE function says are the 1st through n-1th
highest grades.

HTH