You can use the following formula in Excel to calculate the average value in a range and drop the lowest value when calculating the average:
=(SUM(B2:E2)-SMALL(B2:E2,1))/(COUNT(B2:E2)-1)
This particular formula drops the lowest value in the range B2:E2 and then calculates the average of the remaining values.
The following example shows how to use this formula in practice.
Example: Calculate Average and Drop Lowest Value in Excel
Suppose we have the following dataset in Excel that shows the scores received by various students on four midterm exams:
Suppose the professor of this class calculates the final grade of each student by calculating the average value of their midterm grades and excluding the value of their lowest grade on any midterm.
We can type the following formula into cell F2 to drop the lowest value for each student and calculate their average using the remaining values:
=(SUM(B2:E2)-SMALL(B2:E2,1))/(COUNT(B2:E2)-1)
We can then click and drag this formula down to each remaining cell in column F:
Column F now displays the average value for each student with the lowest midterm score dropped for each student.
For example:
Andy’s lowest value of 74 was dropped. His average was then calculated as (90 + 90 + 84) / 3 = 88
Bob’s lowest value of 71 was dropped. His average was then calculated as (92 + 84 + 91) / 3 = 89
The same process was repeated for each student.
How This Formula Works
Recall the formula that we used to calculate the average while dropping the lowest value:
=(SUM(B2:E2)-SMALL(B2:E2,1))/(COUNT(B2:E2)-1)
Here is how this formula works:
First, we use the SMALL function to find the one smallest value in the range B2:E2.
We then use SUM to calculate the sum of all values in the range B2:E2 and then subtract the smallest value from the range.
Lastly, we use a division sign followed by the COUNT function to divide this value by the total count of values in the range B2:E2 with one value subtracted.
The end result is that we’re able to calculate the average value in a range while excluding the lowest value from the calculation.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate Average by Date in Excel
How to Calculate Average by Group in Excel
How to Calculate Average and Ignore Zero and Blank Cells in Excel
This was very helpful. However, I’m trying to do the handicaps for our golf league and the table I have shows averaging only a certain number of the lowest scores for the weeks played. Is there a way to eliminate multiple high values? I don’t know how they came up with the quantities of low scores required but below is the number of scores to be averaged. For the 1st one I just changed SMALL to LARGE in your formula.
2 of 3
2 of 4 (2 being the lowest of the 4 scores)
3 of 5
3 of 6
4 of 7
4 of 8
5 of 9
5 of 10
6 of 11
6 of 12
7 of 13
7 of 14
8 of 15
8 of 16
9 of 17
9 of 18
Hi Gary…Thanks for the detailed explanation — you’re trying to compute golf handicaps by **averaging only the lowest scores** from the total rounds played, based on a lookup table like:
| Rounds Played | Scores Used |
|—————|————-|
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 4 |
| … | … |
| 18 | 9 |
### 🟢 Goal
You want Excel to:
1. **Count** how many rounds (scores) a player has.
2. **Look up** the number of scores to average from the above table.
3. **Pick the lowest N scores**.
4. **Calculate the average** of just those N scores.
—
## ✅ Solution
Let’s assume:
– Player scores are in **cells B2:S2** (up to 18 scores).
– The lookup table (Rounds Played vs Scores to Use) is in range **X2:Y18**.
– Column X = total scores played (e.g., 3, 4, 5, …)
– Column Y = how many scores to average (e.g., 2, 2, 3, …)
—
### 🧮 Formula
“`excel
=AVERAGE(SMALL(FILTER(B2:S2, ISNUMBER(B2:S2)), SEQUENCE(VLOOKUP(COUNTA(B2:S2), X2:Y18, 2, TRUE))))
“`
—
### 🔍 Explanation
1. `B2:S2` → Your row of player scores.
2. `ISNUMBER(B2:S2)` → Filters out blanks or text.
3. `FILTER(…)` → Keeps only actual numeric scores.
4. `COUNTA(B2:S2)` → Counts how many rounds are played.
5. `VLOOKUP(…)` → Finds how many scores to average from your table.
6. `SEQUENCE(…)` → Creates a list like `{1,2,3}` for the N smallest values.
7. `SMALL(…)` → Gets the N lowest scores.
8. `AVERAGE(…)` → Takes the average of those scores.
—
### ✅ Example:
If a player has 6 scores:
`=AVERAGE(SMALL(FILTER(B2:G2, ISNUMBER(B2:G2)), SEQUENCE(3)))`
👉 Averages the lowest 3 scores.
—
### 📝 Notes:
– Adjust the range `B2:S2` to match your data row.
– Make sure your lookup table is sorted by increasing number of scores played.
– If you want to handle errors (e.g., fewer scores than needed), wrap with `IFERROR`.
—