# How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often** calculate the average excluding the smallest and highest numbers** in the range of data. As we know we can use some formula like MAX or MIN to get the maximum value or minimum value in a range, so we can use formula combination to calculate the average excluding the MAX and MIN values. In this article, we will provide you some useful formulas to solve our problem.

**Calculate the Average Excluding the Smallest and Highest Numbers by Formula 1**

**For example, we have a list of scores:**

As we all know, we can use the total amount minus the smallest value and the highest value, then divide the total number-2, so for this case we can use the similar formula combination to calculate the average. Details see steps below.

**Step 1:** In any blank cell, enter the formula **=(SUM(A2:A9)-MIN(A2:A9)-MAX(A2:A9))/(COUNT(A2:A9)-2)**.

**Step 2:** Click **Enter** to get the result. Verify that average is calculated properly.

**Calculate the Average Excluding the Smallest and Highest Numbers by Formula 2**

Above formula combination is a little complex, we can use a special function TRIMMEAN to calculate the average for some conditions.

**Step 1:** In any blank cell, enter the formula **=TRIMMEAN(A2:A9,2/COUNT(A2:A9))**.

**Step 2:** Click **Enter** to get the result. Verify that average is calculated properly. It is the same with the value calculated by the formula combination.

**Calculate the Average Excluding the Smallest and Highest Numbers by User Defined Formula in VBA**

If we frequently use the function to calculate the average in daily work, we can create a user defined function by VBA, then we can use the function directly.

**Step 1: **Click **Developer tab->Visual Basic** or **Alt+F11** to load **Microsoft Visual Basic for Applications** window.

**Step 2: **Click **Insert->Module** to insert a module. Then Module1 is created.

**Step 3:** Enter below code in **Module** window.

Function AveExcludeSmallestHighest(myrange) As Variant With Application AveExcludeSmallestHighest = (.Sum(myrange) - .Max(myrange) - .Min(myrange)) / _ (.Count(myrange) - 2) End With End Function

See screenshot below:

In above code, we create the function as AveExcludeSmallestHighest. You can shorten the function name per your requirement.

**Step 4:** In any blank cell, enter =ave, verify that user defined function **AveExcludeSmallestHighest** is loaded.

**Step 5:** In any blank cell, enter the formula **=AveExcludeSmallestHighest(A2:A9)**.

**Step 6:** Click **Enter** to get the result.

So after above steps, we can directly use this function to calculate the average in following work directly.

### Related Functions

- Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)… - Excel MIN function

The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])…. - Excel MAX function

The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])… - Excel COUNT function

The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…