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
Mastering Excel Slicers: A Comprehensive Guide
Excel slicers are powerful tools that enhance data filtering in Microsoft Excel, making it easier to visualize and analyze your data. In this blog, we will explore how to create and effectively use slicers, as well as their advantages and limitations in comparison to traditional filters.
Chris Menard
Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers.
Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.
Categories