How can I use Excel to calculate the average and drop the lowest value from a set of data?

By /

Excel is a powerful tool that can be used to efficiently calculate and analyze data. To calculate the average and drop the lowest value from a set of data in Excel, you can use the AVERAGE and SMALL functions. First, select the range of data from which you want to calculate the average and drop the lowest value. Then, use the AVERAGE function to calculate the average of the selected data. Next, use the SMALL function to determine the smallest value in the selected data. Finally, subtract the value returned by the SMALL function from the value returned by the AVERAGE function to get the desired result. This method eliminates the need for manual calculations and ensures accuracy in data analysis.

Excel: Calculate Average and Drop Lowest Value

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:

Excel calculate average drop lowest

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:

x

Related terms:

  1. Excel: Calculate Average and Drop Lowest Value
  2. Excel: Calculate Average and Exclude Highest & Lowest Values
  3. How do I highlight the lowest value in Excel?
  4. Highlight Lowest Value in Excel
  5. How can I calculate the average of a data set across multiple sheets in Excel?
  6. Find Lowest 3 Values in Excel (With Example)
  7. How can I calculate the average of a data set in Google Sheets while ignoring zero and blank cells?
  8. Fix: pandas data cast to numpy dtype of object? Check input data with np.asarray(data).
  9. How to use VBA to calculate average value of a range?
  10. Question: How would you calculate the average value of a field in a collection?