The average of a range of data doesn’t always illustrate the perfect pictures. Sometimes there are outliers in the range that causes a deviation from true averages, means, modes, or any other statistics. Occasionally, these deviations can be very drastic. So we need to find the desired values excluding outliers’ values sometimes to get a clear picture. In this article, we will see how to calculate the average excluding outliers in Excel.
Download Practice Workbook
You can download the workbook with the dataset used for demonstration and all the results of different formulas from the download box below.
What Is Outlier?
In statistics, an outlier is a data point that differs from the rest of the data points significantly. In other words, outliers are inconsistencies among statistical distributions. Usually, these values are significantly larger or smaller than the rest of the values. When we combine these outliers with general values, the general idea for a group can often be misunderstood. So, it is very important to identify and remove outliers from some calculations.
Statistically, an outlier abides by two conditions. For outliers that are significantly smaller than the rest, the condition is:
And for larger values, the condition for the outliers is:
Where Q1, Q3, and IQR are 1st quartile, 3rd quartile, and interquartile range respectively.
4 Easy Methods to Calculate Average Excluding Outliers in Excel
We can use different functions, or combinations of functions to either ignore outliers while calculating averages or eliminate outliers altogether and then calculate the average or other calculations in Excel. Below is a demonstration of 4 methods that can help us to calculate the average excluding outliers in Excel. For every method, we are going to use the following dataset to calculator the average.
Here, we can see that there are two outliers in this dataset. In every method, we are going to calculate the average age of all people here ignoring these outliers.
1. Using TRIMMEAN Function
The TRIMMEAN function in Excel returns the average of a range of data excluding a top or bottom percentage of the values. It takes the array and the percentage to be ignored as the arguments. Follow these steps to see how you can use this function to calculate the average excluding outliers in Excel.
Steps:
- First, select a cell to enter the average (we have selected cell C16 here) and write down the following formula.
=TRIMMEAN(C5:C14,0.2)
- Then press Enter on your keyboard. You will now have the average calculated excluding the outliers for this dataset.
What this function did here was take the range C5:C14 as the array argument and ignored 20% of the values (2 out of 10 = 0.2 as the second argument) that included the top and bottom outliers combined. Then the function returned the average for the rest of the data.
Read More: How to Find Outliers in Regression Analysis in Excel (3 Easy Ways)
2. Applying AVERAGEIFS Function
Excel has a function called the AVERAGEIFS function that can be used to calculate averages excluding outliers if done right. Unfortunately, if you use this function only to do the task, you have to manually input the outliers in the function.
This function takes the average range, different criteria ranges, and conditions as its arguments consecutively. It then returns the average of the range as its result. If we use the condition that eliminates the outliers with inequality conditions, we can easily calculate the average of a range excluding the outliers in Excel. Follow these steps for a more detailed guide.
Steps:
- First, select a cell to calculate the average and write down the following formula in it.
=AVERAGEIFS(C5:C14,C5:C14,">20",C5:C14,"<26")
- After pressing Enter you will finally have the average of the range in the dataset excluding the outliers.
🔍 Breakdown of the Formula
AVERAGEIFS(C5:C14,C5:C14,”>20″,C5:C14,”<26″)
👉 The AVERAGEIFS function calculates the mean of cell values that satisfy multiple criteria. Here, It considers only those values in range C5:C14 that are greater than 20 and also less than 26. So, this formula calculates the mean of values in range C5:C14 that fall between 20 to 26.
Read More: How to Calculate Outliers in Excel (5 Easy Ways)
3. Use of Interquartile Range
In the outliers section, we have seen the conditions of outliers with interquartile range. In statistics, an interquartile range is the distribution of the middle section of the dataset. We can easily calculate it by subtracting the 1st quartile from the 3rd in Excel. This can be done with the help of the QUARTILE function in Excel.
We can then use the OR function to determine which of the values are outliers in a range. This function takes different conditions as arguments and returns a boolean value depending on whether all the conditions are true or not. Then we can find the average for all the values eliminating the outliers with the help of the AVERAGEIFS function.
Follow these steps for an in-depth guide to the process.
Steps:
- First, select a cell for the interquartile range and write down the following formula in the cell.
=QUARTILE(C5:C14,3)-QUARTILE(C5:C14,1)
- Now press Enter and you will have the interquartile range of the dataset.
- After that, select cell D5 and write down the following formula.
=OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16)
- Then press Enter and you will have a boolean value of whether the value of cell C2 is an outlier in the range or not.
- Next, select the cell again and click and drag the fill handle icon to fill the rest of the cells of the column with the formula.
- After that, select a cell for the average and write down the following formula.
=AVERAGEIFS(C5:C14,D5:D14,FALSE)
- Finally, press Enter. As a result, this will calculate the average of the dataset excluding outliers in Excel.
🔍 Breakdown of the Formula
OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16)
👉 QUARTILE($C$5:$C$14,1) function takes the range C5:C14 as the array and returns the first quartile of this array.
👉 C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16 is a condition that checks if the value of cell C5 is less than the difference between the first quartile of the range C3:C14 and 1.5 times of value in cell D16. Cell D16 here contains the interquartile range of the data.
👉 Whereas C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16 condition checks if the value of cell C5 is greater than the sum of the third quartile of the range C5:C14 and 1.5 times the value of interquartile range which is at cell D16.
👉 Finally, OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16) checks whether any of the two conditions described above is true or not. If either one is true, it returns the boolean value TRUE as output. Else, it returns FALSE.
Read More: How to Find Outliers in Excel (5 Useful Ways)
4. Utilizing SUMPRODUCT Function
There is another function in Excel that can be utilized to calculate the average excluding outliers. It is called the SUMPRODUCT function. This function takes different ranges as arguments and returns the sum of the product of the corresponding range. We will also take the help of the QUARTILE function.
We can then use the OR function to determine which of the values are outliers in a range. This function takes different conditions as arguments and returns a boolean value depending on whether all the conditions are true or not.
Follow these steps to see how you can use the function to calculate the average excluding the outliers in Excel.
Steps:
- First, select a cell for the interquartile range and write down the following formula in the cell.
=QUARTILE(C5:C14,3)-QUARTILE(C5:C14,1)
- Then press Enter and you will have the interquartile range of the dataset.
- After that, select cell D5 and write down the following formula.
=OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16)
- Now press Ener and you will have a boolean value of whether the value of cell C2 is an outlier in the range or not.
- Next, select the cell again and click and drag the fill handle icon to fill the rest of the cells of the column with the formula.
This will identify the outliers which are in cells D7 and D10.
🔍 Breakdown of the Formula
OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16)
👉 QUARTILE($C$5:$C$14,1) function takes the range C5:C14 as the array and returns the first quartile of this array.
👉 C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16 is a condition that checks if the value of cell C5 is less than the difference between the first quartile of the range C3:C14 and 1.5 times of value in cell D16. Cell D16 here contains the interquartile range of the data.
👉 Whereas C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16 condition checks if the value of cell C5 is greater than the sum of the third quartile of the range C5:C14 and 1.5 times the value of interquartile range which is at cell D16.
👉 Finally, OR(C5<QUARTILE($C$5:$C$14,1)-1.5*$D$16,C5>QUARTILE($C$5:$C$14,1)+1.5*$D$16) checks whether any of the two conditions described above is true or not. If either one is true, it returns the boolean value TRUE as output. Else, it returns FALSE.
- Then select a cell for the average and write down the following formula in it.
=SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)*(C5:C14))/SUMPRODUCT((C5:C14>C7)*(C5:C14<C10))
- Finally, press Enter and you will have the average of the dataset excluding outliers with the help of the SUMPRODUCT function in Excel.
🔍 Breakdown of the Formula
SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)*(C5:C14))/SUMPRODUCT((C5:C14>C7)*(C5:C14<C10))
👉 C5:C14>C7 is a condition used in the formula. It iterates for all the values in the range C5:C14 and checks which are larger than C7. Note that, cell C7 was selected as it contained an outlier. This returns a boolean array for all the values in the range.
👉 C5:C14<C10 condition iterates all the values in range C5:C14 and checks if each is less than C10 or not, which is another cell that contains an outlier.
👉 (C5:C14>C7)*(C5:C14<C10)*(C5:C14) portion of the function returns the true results and false values become 0 in the multiplication process. (TRUE has a value of 1 and FALSE as 0 for arithmetic operations). In the process, outliers get eliminated before summation.
👉 Now SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)*(C5:C14)) returns the sum of all the members of the array.
👉 (C5:C14>C7)*(C5:C14<C10) is an array consisting of only true or false values in 1s and 0s. This arithmetic operation returns 1 where both the conditions are true and 0 if either one is false, thus eliminating the outliers.
👉 SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)) returns the summation of all the members of the previous array. And thus returns the total number of data excluding outliers.
👉 Finally, SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)*(C5:C14))/SUMPRODUCT((C5:C14>C7)*(C5:C14<C10)) divides the first sum-product of the array by the second one, giving us the average of the data where outliers are absent.
Read More: How to Show Outliers in Excel Graph (2 Effective Methods)
Conclusion
These were all the methods including all the functions you can use to calculate average excluding outliers in Excel. Hope you will be able to confidently use the functions and calculate the averages and outliers for your customized datasets in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like this, visit Exceldemy.com.
Hi, in the breakdown in the formula:
OR(C5QUARTILE($C$5:$C$14,1)+1.5*$D$16).
Shouldn’t use ($C$5:$C$14,3) instead?
Thanks!
Greetings AND74,
Thanks for noticing the error.
It will be OR(C5QUARTILE($C$5:$C$14,3)+1.5*$D$16) instead of OR(C5QUARTILE($C$5:$C$14,1)+1.5*$D$16).
We will make the corrections shortly.
Best Regards,
Bishwajit
Team ExcelDemy