Mean of Values EXCLUDING two lowest values

[acpharmd]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

Gradebook help question. I need to calculate the mean of several scores after dropping the two lowest scores. I found the MIN function, but cannot determine how to omit the TWO lowest.
Thank you in advance.

 

[JE McGimpsey]

One way:

=SUM(rng,-SMALL(rng,{1,2}))

or, equivalently (array-entered with CMD-RETURN):


=SUM(LARGE(rng, ROW(INDIRECT("1:"&COUNT(rng)-2))))

 

[acpharmd]

I have tried this formula with fictitious grades and everything worked wonderfully, but when I apply it to my nonconsecutive range - which contains zeros, it gives me an error.

Thanks for this - it really opened a door for me to consider. If you are able to help with my current problem described above, I have attached the formula as I entered it. All are postive integers except for U3 which has a value of zero (0).

=(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400

Thanks again.

 

[PhilD]

=(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400

Would you need a separate set of parentheses around your P3,R3, ...
(etc)?

PhilD

 

[JE McGimpsey]

I have tried this formula with fictitious grades and everything worked
wonderfully, but when I apply it to my nonconsecutive range - which contains
zeros, it gives me an error.

Thanks for this - it really opened a door for me to consider. If you are able
to help with my current problem described above, I have attached the formula
as I entered it. All are postive integers except for U3 which has a value of
zero (0).

=(SUM(P3,R3,U3,X3,AA3,AD3,-SMALL(P3,R3,U3,X3,AA3,AD3,{1,2})))/400

One way (array-entered: CMD-RETURN):

=SUM(P3:AD3, -SMALL(IF(ISNA(MATCH(COLUMN(P3:AD3),
{16,18,21,24,27,30},0)), "", P3:AD3), {1,2}))