Excel Formula - Average Excluding Outliers in a Range
HTML-код
- Опубликовано: 20 окт 2016
- 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;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
![mgk - I Think I’m OKAY (with YUNGBLUD) (Sad Version) [Official Music Video]](http://i.ytimg.com/vi/7AC1uqQLIg4/mqdefault.jpg)
Thank you for sharing!
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.
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.
Wow thank you!
You're welcome!
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.
cheers
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.
Done thanks
Uses trimmean function
Welcome 👍
Hi Alan.. good one.. was not familiar with TRIMMEAN. See comment below.. blank cells are ignored by both functions.. yes? Thanks and Thumbs up!
Yep. Thanks Wayne.
Merhaba ben Türkiye'den soccer ht/FT halfteam fulteam bunun için algoritma ?
Were the blank cells in B2:B13 counted in the average as zero, or ignored?
Hi Wayne.. I was curious too. I tested and it appears blanks are ignored for both AVERAGE and TRIMMEAN. Alan.. yes?
How do you know what percentage to use...? Is there a way to find out what numbers were excluded? Thank you.
You guess really. Depending on where you see the fringe numbers. The idea is that there would be too many numbers to be that specific.
Just wanted to note that this works in Google Sheets also!
Awesome Matt. Thanks.
yes.
How would I know how much percentage I have to take sir?
It is your own judgement depending on how extreme an outlier you want to ignore.
@@Computergaga if the data set is big than having that idea is very difficult.
Yes, it is just an estimate to ignore extreme outliers. The average of the data is the average. This is just to ignore extreme outliers that may distort the result more than you wish. So the percentage depends on your wishes. It is all relative.
@@Computergaga thanks for support sir. Pressed the bell icon already. 😀
You're welcome, Ashish.