Grade calculation sheets

[Guest]

To calculate quiz averages from ten quiz grades I'd like
to drop the two lowest grades and then average the rest.

Is there a function, or combination of functions,in Excel
that will do this?

Thousands of teachers must have confronted this question
before as they put together spread sheets to record
grades, and calculate averages -but as a beginning user
of Excel it sure is a mystery.

Thanks for any help,
Carl

 

[Jim Rech]

From the sum of the grades you'd want to subtract the smallest and the
second smallest. If the grades are in A1:A10 the smallest is:

=MIN(A1:A10) [or =SMALL(A1:A10,1) ]

and the second smallest is:

=SMALL(A1:A10,2)

You would then divide by the number of test scores less 2, which is:

=COUNT(A1:A10)-2

This assumes at least 3 tests were taken. You could divide by

=MAX(1,COUNT(A1:A10)-2)

to never get a zero or negative test count.

 

[Peo Sjoblom]

One way using array formula

=AVERAGE(LARGE(A1:A10,ROW(1:8)))

entered with ctrl + shift & enter

or

=SUMIF(A1:A10,">"&LARGE(A1:A10,9))/8

they will differ if for instance you have several values that are equal like
if
the 2 lowest values are 4 and they are all the same