Excel Formula - Average Excluding Outliers in a Range

Calculate the average excluding outliers in Excel. Outliers are numbers that are outside the typical range and can affect the average result.

To ignore these outliers the TRIMMEAN function is used. This function calculates the mean average, but allows for a percentage of numbers to be recognised as outliers.

This video tutorial shows this formula being used to calculate the average exam score from a range.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[missanna]

This is the kind of thing I never learned in stats, I don't think. Wish I would have googled this question yesterday. Bless your heart for sharing this.

[mlhess]

Another way to compensate for extreme outliers is by using the median function. Whereas you might have to justify why you decided on the percentage you chose for the trimmean function, the median function finds the value in the middle of the range (in this case it is 74.5 because you have 10 values so it takes the average between 72 and 77, but in an odd numbered range you would have an exact middle value and it would take that), making the median a more accurate estimate without having to justify why you are lopping off a certain percentage of values.

[wayneedmondson]

Hi Alan.. good one.. was not familiar with TRIMMEAN. See comment below.. blank cells are ignored by both functions.. yes? Thanks and Thumbs up!

[TheRustAdmin]

I have quite an urgent question: How do I extend the upper bound whisker of my box and whisker plot to include outliers? Excel has deemed a couple of data points unreasonable, but they're not, and I want the highest of them to be my maximum, but the whisker stops at a lower datum. Thanks for any advice.

[DonaldAnderson]

Just wanted to note that this works in Google Sheets also!

[pipo]

How do you know what percentage to use...? Is there a way to find out what numbers were excluded? Thank you.

[waynebrown]

Were the blank cells in B2:B13 counted in the average as zero, or ignored?

[kreras]

Hi. I have a question. I have a dataset which is composed of two columns. On the left, I have a value between 0 and 90 (I think it is a trigonometric function, meaning an angle in degrees in fact) and on the right, I have a value typically between -3 and +3. What I want to do is I want Excel to, in a separate worksheet, give me for a given value of an angle (between 0 and 90 that I will specify); a count of how many values lie within 15 degrees of the value I have specified, divided into in fact 30 different classes going one degree up in each case. I also want excel to give me the average value inside each of these 30 cells, of the drawn values that fall within the range. So if I put say 20 degrees, the first category should be 5 to 6 degrees, and an average inside that, then 6 to 7 idem, and finally, 34 to 35 degrees, with an average. I want to then take those averages and using a second weighted average method, estimate mean and standard deviation for the angle i specified (of 20) of all readings. Since the values are positive and negative, I want excel to tell me how many are positive and how many negative, and give me in fact mean and standard deviation for all positive and all negative values for that angle. Thanks.

[fehmikonkur]

Merhaba ben Türkiye'den soccer ht/FT halfteam fulteam bunun için algoritma ?

[AshishSharma-rvmz]

How would I know how much percentage I have to take sir?