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:
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:
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.
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:
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.
The following tutorials explain how to perform other common tasks in Excel: