Excel Drop Lowest Grade: Formula & Steps

This guide explains how to exclude a student’s lowest grade from their final average in Microsoft Excel, covering the core formulas, step-by-step instructions, and common pitfalls. You will learn the exact formula to drop the single lowest score, how to adapt it for multiple drops, and how to handle blank cells and zeros.

Formula

The formula to drop the lowest grade and average the rest is:

Average = (SUM(grades) – MIN(grades)) / (COUNT(grades) – 1)

• SUM(grades) – the total of all grade values (points).
• MIN(grades) – the smallest grade value (points).
• COUNT(grades) – the number of grades (count).

This formula subtracts the lowest grade from the total, then divides by the number of remaining grades. It assumes each grade has equal weight. If grades have different weights (e.g., some are worth double), you cannot use this simple formula.

Worked Example

Using the default values: grades = 85, 90, 78, 92, 88.

1. Sum of all grades: 85 + 90 + 78 + 92 + 88 = 433
2. Lowest grade: 78
3. Subtract lowest: 433 – 78 = 355
4. Count of grades: 5
5. Remaining count: 5 – 1 = 4
6. Average: 355 / 4 = 88.75

The calculator above returns 88.75, matching this result.

How to Drop the Lowest Grade in Excel (Step-by-Step)

In Excel, you can use the same logic with cell references. Suppose your grades are in cells B2 through B6.

1. In a blank cell, type: =(SUM(B2:B6)-MIN(B2:B6))/(COUNT(B2:B6)-1)
2. Press Enter. Excel calculates the average without the lowest grade.
3. If you want to drop more than one lowest grade, use the SMALL function. For example, to drop the two lowest grades: =(SUM(B2:B6)-SMALL(B2:B6,1)-SMALL(B2:B6,2))/(COUNT(B2:B6)-2)
4. Alternatively, use the TRIMMEAN function for dropping a percentage of grades, but that is not the same as dropping a fixed number.

Choosing Between Dropping Lowest vs. Using TRIMMEAN

If your goal is to drop the single lowest grade, the SUM-MIN formula is the most straightforward. However, if you need to drop a percentage of the lowest and highest grades (e.g., drop the bottom 10% and top 10%), use TRIMMEAN. The TRIMMEAN function is symmetric – it removes both low and high extremes, which is not what you want when only dropping the lowest.

For most classroom grading, dropping the lowest assignment is a fixed count, not a percentage. The SUM-MIN method is clearer and easier to audit.

Common Mistakes and How to Avoid Them

• Including blank cells – If a student has not submitted an assignment, the blank cell is treated as 0 by MIN, which would drop that zero instead of the actual lowest grade. Use IF or filter blanks before applying the formula.
• Dividing by zero – If all grades are blank or only one grade exists, the formula returns a #DIV/0! error. Add an error check: =IF(COUNT(B2:B6)>1, (SUM-MIN)/(COUNT-1), "Not enough grades")
• Mixing points and percentages – The formula works only if all grades are on the same scale. If some are out of 10 and others out of 100, you must normalize them first.
• Rounding too early – Do not round the intermediate sum or min; round only the final average to avoid rounding errors.

Alternatives: Using Array Formulas or AVERAGEIF

Another approach is to use an array formula that excludes the minimum value. In modern Excel, you can use:

=AVERAGE(IF(B2:B6<>MIN(B2:B6), B2:B6))

Enter it with Ctrl+Shift+Enter if you are using older Excel versions. This formula averages all values except those equal to the minimum. Note that if there are duplicate minimums, it drops all of them, which may not be desired. The SUM-MIN formula drops only one occurrence, which is usually the correct behavior.

For a more flexible solution, consider using the SMALL function to exclude a specific number of lowest grades, as shown earlier.

Frequently Asked Questions

How do I drop the lowest grade but keep zeros?

If a student has a zero for a missing assignment, that zero is the lowest grade and will be dropped, which might be unfair. To keep zeros and drop the next lowest, you need to exclude zeros from the MIN calculation. Use an array formula like =MIN(IF(B2:B6>0, B2:B6)) to find the lowest positive grade, then subtract that from the sum. However, this changes the semantics – you are no longer dropping the absolute lowest grade.

Can I drop the lowest grade for each student in a list?

Yes, if you have grades in rows per student, you can copy the formula down for each row. For example, if student 1 grades are in B2:F2, the formula in G2 would be =(SUM(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-1). Drag the fill handle down to apply to other students.

What if I need to drop the lowest grade and then weight the remaining grades?

If grades have different weights, you cannot use the simple average. You need to calculate a weighted average after excluding the lowest grade. One way is to use the SUMPRODUCT function with a condition that excludes the lowest grade. For example, if weights are in C2:C6, use =SUMPRODUCT((B2:B6<>MIN(B2:B6))*B2:B6*C2:C6)/SUMPRODUCT((B2:B6<>MIN(B2:B6))*C2:C6). This is more complex but necessary for weighted scenarios.

Does Excel have a built-in “drop lowest� function?

No, Excel does not have a single built-in function specifically for dropping the lowest grade. You have to combine functions like SUM, MIN, and COUNT, or use array formulas. Some newer versions of Excel have a TAKE or DROP function for arrays, but they are not designed for this purpose.

Why does my formula return a #DIV/0! error?

This error occurs when the denominator (COUNT-1) is zero, meaning there is only one grade or no grades. Add an IF condition to check the count before dividing, as shown in the common mistakes section.

Can I drop the lowest grade from a percentage-based grading system?

Yes, the formula works the same if all grades are percentages. Just ensure they are in decimal or percentage format consistently. The result will be the average percentage after dropping the lowest.

Related

• Page B Score Calculator: How It Works
• Standard Score to Z Score Calculator
• Test Score Calculator by Points: How It Works

in t | Total points possible | points | 100 | 1
in g | Grades (comma-separated) | - | 85, 90, 78, 92, 88 | 0
out avg | Average after dropping lowest | points | (sum(g) - minl(g)) / (count(g) - 1) | 2