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 Sorting Tips with Chris Menard on January 24, 2023

Excel Sorting Tips with Chris Menard on January 24, 2023

Excel - Convert Seconds to Minutes and Seconds - Watch Chris Work!

Excel - Convert Seconds to Minutes and Seconds - Watch Chris Work!

TOP 5 Excel Features Released in 2022

TOP 5 Excel Features Released in 2022

Getting Started With PivotTables - Live on 2-22-2023

Getting Started With PivotTables - Live on 2-22-2023

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 over 900 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 15 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube

Categories