Excel AVERAGE excluding the highest and lowest values | TRIMMEAN Function
Posted on: 04/14/2022
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
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
-
Insert Online Videos in Microsoft Word and Troubleshooting Tips
Inserting videos from the web inside a Word document is easy. You can link to a video from a document, using the Insert Hyperlink function, but you are also able to insert the actual video in Word, so it shows the thumbnail and you can access playback.
-
Microsoft Word's incredible Follow-up feature!
Have you ever been in a Word document and need to mark some sections for Follow-up? For example, a bulleted or numbered list where you need to come back and some bullet points. Maybe you don't like the wording of a paragraph and want to follow-up on it later.
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
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
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
TOP 5 Excel Features Released in 2022
Microsoft keeps rolling out awesome new features for M365 users. Many of the new features hit Excel in 2022. This video covers my top five favorite features in Excel from 2022.
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
