How to drop the lowest grade and get the average or total of values in Excel?
When you are working with a list of grades or scores in Excel, you may need to calculate a student's final grade by excluding their lowest score, or even the lowest n scores, before averaging or summing the remaining values. This is a common requirement in educational settings, where students might be allowed to drop their worst performances to account for anomalies or to ensure fairness. Conducting this operation manually could be tedious, especially with large datasets or frequent calculation adjustments. Fortunately, Excel provides several flexible ways to achieve this, ranging from straightforward formulas to automation with VBA for batch operations.
Drop the lowest grade and get the average or summation with formulas
VBA Code - Drop the lowest or lowest n grades and calculate the sum or average automatically
Drop the lowest grade and get the average or summation with formulas
If you want to exclude the lowest or lowest n values from a data row or list and then perform calculations—such as averaging or summing the remaining numbers—Excel's built-in formulas offer a practical approach. These formula solutions are especially useful if you have a moderate number of rows to process or prefer a formula-based approach for transparency and ease of adjustment.
Below, you'll find separate explanations for both summing and averaging methods, including detailed instructions and practical tips for flexible application.
Sum the numbers but drop the lowest or lowest N numbers:
To calculate the sum for each row or list while omitting the lowest value, use the following method:
1. Select a blank cell where you want the sum result for the first row to appear (for example, in cell I2 if your data is in B2:H2), then enter the following formula:
=SUM(B2:H2)-SMALL(B2:H2,1)2. Press Enter to confirm, then drag the fill handle down to apply this formula to other rows as needed. This will sum all numbers in each row, excluding the lowest value.
Here’s a screenshot for reference:
Notes and tips:
- To exclude the lowest two, three, or more values, you can extend the formula by subtracting additional SMALL results. For example:
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- In these formulas, B2:H2 is the range that you want to sum, and the numbers 1, 2, 3, etc., specify the n smallest numbers to exclude. Adjust n based on how many of the lowest grades you wish to drop.
- Be careful not to set n greater than or equal to the total number of values; otherwise, you'll encounter errors or unwanted results.
- These formulas work independently for rows. If your data spans columns instead of rows, adjust the ranges accordingly.
- If your dataset contains duplicates of the lowest number, SMALL(B2:H2,1) will only drop one occurrence per reference. To drop multiple occurrences, repeat the SMALL term with incremented k numbers as shown above.
Average the numbers but drop the lowest or lowest N numbers:
To calculate the average while ignoring the lowest or lowest n values, you can use the formulas below. This calculation is especially useful in grading schemes where outlier low performances are not to be considered in averaging.
1. Select a cell for the average result (for instance, J2 if your scores are in B2:H2), and enter the formula:
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2. After pressing Enter, drag the formula down as needed to average additional rows, each time omitting the lowest score in the range for that row.
Notes and important guidance:
- To average while dropping more than one lowest score, expand the formula by subtracting additional SMALL terms and reducing the divisor accordingly:
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- Again, B2:H2 is the range to average, and n represents how many lowest values will not be included in the calculation.
- If you attempt to subtract more numbers than exist in the range, formulas will return a #NUM! error, indicating insufficient values for averaging. Always ensure n is less than the count of numbers.
- It's recommended to double-check that the lowest values are not critical or required for your calculation before excluding them, as this may impact final outcomes.
- For extremely large datasets or dynamic dropping of the lowest n values, consider an automated or array solution.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
VBA Code - Drop the lowest or lowest n grades and calculate the sum or average automatically
For situations involving large or frequently changing datasets, or where you need to automate dropping the lowest n grades and computing sums or averages across many rows, using VBA can greatly simplify repetitive work. With a VBA macro, you can specify the range of data and how many lowest grades to exclude, and the code will process all selected rows efficiently in one step.
This automatic approach is especially useful for teachers managing sheets for multiple classes, or anyone who wants to minimize manual formula entry and potential errors. The solution below allows you to easily adjust both the exclusion number and target function (sum or average).
Before you start, please save your workbook, as running macros cannot be undone directly.
1. Click Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then input the following code:
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. After adding the code, click the 
button or press F5 to execute.
3. Follow the prompts that appear:
- Select the score range you wish to process (ensure each student's scores are in a row).
- Choose the output range's top-left cell (the output will populate downward based on row count).
- Enter the number of lowest scores to drop (for example, 1 to exclude only the lowest grade in each row).
- Type SUM to get the total (excluding dropped grades) or AVG to get the recalculated average (excluding dropped grades).
The macro processes each row from the specified score area and places either the sum or average (as chosen) into your output range. If all scores are dropped in a row, the result is marked as N/A to avoid errors.
- Ensure the input range matches your data structure (one student's scores per row).
- Non-numeric cells (e.g., blank or text) will be ignored by default.
- This VBA code greatly accelerates repetitive grading calculations for whole classes and supports flexible adjustment of the number of grades to drop.
- If you frequently perform such operations, you may wish to assign this macro to a button on your worksheet for even quicker access.
If you encounter problems such as incorrect outputs or errors, double-check that ranges are accurately specified and that "n" is not greater than or equal to the total number of available grades for each row.
For similar automation requirements, such as dropping both the highest and lowest scores or handling columns instead of rows, slight adjustments can be made to the VBA code logic.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in