How to calculate average without max and min values in Excel?
As we all known, the normal Average function will get the average value of all the selected cells in Excel. But here, you want to calculate the average excluding the highest and lowest values from the selected numbers, how could you solve this problem?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following combined functions can help you to average a range of values without the max and min numbers, please do as this:
You can enter one of the above formulas into a blank cell, see screenshot:
Then press Enter key, and you will get the average result which ignoring one largest and one smallest number.
Note: In the above formulas, A2:A12 indicates the data range that you want to calculate, you can modify it as you need.
Besides the formulas, you can solve this task by creating a User Defined Function, do as following steps:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
Function Avewithoutmaxmin(region) As Variant 'Update 20140325 With Application Avewithoutmaxmin = (.Sum(region) - .Max(region) - .Min(region)) / _ (.Count(region) - 2) End With End Function
3. Save and close this code, then back to the worksheet, and enter this formula =Avewithoutmaxmin(A2:A12), (A2:A12 indicates the data range that you want to calculate the average excluding the max and min values, you can modify it as you need.) see screenshot:
4. Then press Enter key, and you will calculate the average value which excluding the max and min numbers.