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: