How to Find Outliers with Excel 

Glad it helped!

Outliers live outside the inner quartile range. By statistical definition, they live 1.5 times below or above your inner quartile range.( copied from the link he ut in the descriotin box)

I am glad this brightened up your day!!!

@@absentdata no. it brightened up my life.

Keep going dude! Stay strong!

@@tentacleprincess5772 i am strong now because of absent data

Happy that you subscribed😁

You are most welcome

It depends on the data set. For example even if you had a outlier boundaries that's negative, it doesn't always apply. For examples a child birth dataset might not use any negative outliers then you would just use 0 as your range.

1.5 is a constant that is used in this statistical formula. Think of it like PI

@@absentdata I found that I had to change that 1.5 to 1 for the dataset I was working with for it to pick up other values I considered outliers as well. Will do some more research on that factor and what it is exactly but I had to change it to get a better result.

That's so great

Happy to help!

Glad to hear it

In a sense yes, you may be doing a univariate analysis of the three businesses. For example you may be looking at Revenue across all the three businesses in comparing their distributions. You can compare the shape of the distribution whether it's a normal distribution or a very skewed distribution. Skewed distributions are affected by outliers.

@@absentdata Thank you so much!!

I have the same problem

This is part of the accepted original formula. Essentially quartiles can be scaled to standard deviations. The math equates 1.5×IQR to under 3 standard deviations from the mean. So the 1.5 was designed in the formula to capture the majority of outliers assuming the data is normally distributed.

No it should be fine.. However it depends on the data. Should you have negatives or not might be a better question

Probably indicates that your data is very normally distributed with no outliers

@@absentdata would you mind if I showed you my data and say if you think I have correctly identified the outliers?

@@absentdata also my data are decimals so like 0.0347 etc. Would that affect the method at all?

Works on negative outliers also

You could do that by using an if function

You’re welcome 😊

This is standard multiplier for outlier detection. However you can increase it

Thanks I'm glad you solved the issue in the end

Well a column should be seen as a unique dimension in your data. so if you have 20,000 columns. By Excel only has 16,384. I would question the structure of your data. I would see big you can melt the columns down to rows

Its hard to say without having the actual file. There are a host of reasons.. data is not a number incorrect formula

@@absentdata Ok thank you for that!

Welll not really because that invalidates the need for calculating the quartiles. However, I think the approach you are trying to take is more maual where you are setting the boundaries for the outliers yourself.

@@absentdata yep, I work with big listings at my work and we look for values that out of range based on unique base values. Thank you!

I am glad you enjoy the way I teach. Yes, I want to do a purely statistic playlist. Hopefully there is enough interest :)

Is it on the keyboard? What if I’m using excel on an iPad

⌘⌥R or Command T in Mac I believe it will help you lock the position of the cells. BTW I've never had a Mac so I am hoping Google is correct :)

Yes, its a best practice to use 1.5. However, you can alter it but then release that you will lose data. It conforms to a standard normal distribution which means that it's a reliable way of saying that most of your data falls under this about 97.5%

Ryujinne Juano press( fn ) (f4) it would work

Do you just 3 rows of data? That would be the best use of this.

@@absentdata No. I mean just three data. I conducted an analysis with 3 trials so I generated 3 data points. And I need to know whether I can average all those three and report it. Edited** What I meant with "analysis" is "experiment"

@@ellereal8216 Yes that could work for your specific application.

@@absentdata Is that the best option for my case to use to identify the outlier? Can you advise me some other stat tools I can easily apply on a calculator.

@@ellereal8216 Unfortunately I can't really prescribe what to do cause I would need to know your data and context. However I think you can just explain your logic when presenting your findings. You can also can also use percentiles.

I am not sure the test has a name. It's more of a technique to classify the borders of your data.

in Mac is 'Command + t' or '⌘+t'.

1.5 is a constant value that I used in the equation

it is just a statistical rule

Its a statistical formula. Its similar to using a constant like 3.14 for PI

Yes, absolutely. However then you are going to be dealing with standard deviation for normal distribution. This would be a different approach

@@absentdata Thanks！