Information Technology

Tech Tip

Dropping lowest grades in Excel

If you're dropping the lowest grade(s) in your final grade calculations, Excel can do the work for you!

This example is for one student, for whom I have 19 grades. I want to drop the lowest four. The student's data is in cells B2 to T2.

The final formula to drop four of those 19 grades will look like this, and is broken down below:

=(SUM(B2:T2)-SMALL(B2:T2,3)-SMALL(B2:T2,2)-SMALL(B2:T2,1)-SMALL(B2:T2,4))/(15)

=(SUM(B2:T2)

add everything up within that range

-SMALL(B2:T2,1)

subtract the (1st) smallest value w/in that range (for the second, change that to ,2 - etc)

... then divide by number of remaining cases. (in this case, 15).

For more Excel help, check-in at the helpdesk in the ETC, or browse other tech tips. Happy grading!

Tags:

Posted: May 15, 2014

See more Tech Tips