Excel AVERAGE excluding the highest and lowest values | TRIMMEAN Function

Posted on:  04/14/2022
Excel AVERAGE excluding the highest and lowest values | TRIMMEAN Function

When you have a range of numbers in Excel and want to figure out the AVERAGE but you have some extreme values that can skew your averages, there is a function that helps solve that problem.

Average using Sum with Max, Min, and Count

Average using Sum with Max, Min, and Count

You can calculate the AVERAGE in Excel using the TRIMMEAN function, which will allow you to exclude lowest or highest values from being considered for the averages. Further, you can exclude one or more highest/lowest values in your range, by specifying how many you want to ignore.

Recent Word articles

In this article and video I walk you through several methods of calculating this average, using the classic AVERAGE function, then using the MIN and MAX functions to exclude values from consideration, and finally, I show you the TRIMMEAN function in a formula, to exclude extreme values automatically from your averages.

YouTube video

Recent Teams articles

Method 1 - Average with Max and Min Functions

To find the average and exclude the highest and lowest number, we can sum the range of number, subtract the max and min, which are the highest and lowest, and divide by the couunt of the numbers minus 2. See the screenshot at the top of this post.

Method 2 - Use the TRIMMEAN Function

TrimMean Function in Excel

TrimMean Function in Excel

The TRIMMEAN function has two arguments. Both are required argument. In my example, I have 20 numbers that go from A2 to A21. The percentage is 10% or .1 in my example Twenty numbers times 10% is 2. Two numbers are excluded from the mean. The two numbers are the largest and the smallest.

If I made the percentage 20%, that would be four numbers, 20 x  2%=4. The Average would exclude the top two highest and the lowest two numbers from the average.

Syntax

TRIMMEAN(array, percent)

Recent Excel articles

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Recommend PivotTables - Improved Experience

Excel Recommend PivotTables - Improved Experience

Excel PivotTable - Calculate the number of days of each week for every month of the year

Excel PivotTable - Calculate the number of days of each week for every month of the year

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with other 750 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 10 million viewers have appreciated.

Categories