Cleaning data

[ZochSteveo]

Hi there,

I have been trialling a method to determine which are outliers in my data set.

Currently, I have been using Z-scores to do this. I then see if this absolute value is greater than 3 to give an output of whether or not it is an outlier from the rest of the population.

I also want to return that it is an outlier if its Z score is less than -3.

My current formula, using an example, looks like this:

=IF(ABS(AA2)>3,"YES","NO")

What would I change this to so that it also includes if ABS(AA2)<-3 gives an output?

Many thanks,

Zach

 

[MARK858]

Perfect, that works.

What works? your original formula gives the same result.

 

[ZochSteveo]

What works? your original formula gives the same result.

The formula you suggested. This one - =IF(OR($AA2<-3,$AA2>3),"YES","NO"), I think maybe I got a touch confused. Thank you very much for your help. You have been brilliant. I don't suppose there is a method for highlighting all the results that come back with "YES"

 

[MARK858]

Select your range with the YES/NO then in conditional formatting click Highlight cell rules, click Equal to.. , and in the box that says "Format cells that are equal to.." type "YES" (with no quotes), select your format and click ok

 

[ZochSteveo]

Legend! Thank you so much for all your help

 

[MARK858]

You're welcome