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]

ABS(AA2)<-3

can't be less than -3,

ABS(AA2)

it can only be a positive number

 

[ZochSteveo]

ABS(AA2)<-3

can't be less than -3,

ABS(AA2)

it can only be a positive number

Ok thank you, is there an alternative then for what I could do? Cause I do also want to eliminate the small values, below -3

 

[MARK858]

You just remove the ABS and put both formulas in an OR statement

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

 

[ZochSteveo]

You just remove the ABS and put both formulas in an OR statement

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

Does the ABS function need to be removed from both? I have trialled it and it has come through as 'YES' when I was expecting it to be 'NO'

 

[MARK858]

The ABS can be removed as it only changes negative numbers to positive so has no effect on positive numbers, the formula gives the results I expect

Book1
	AA	AB	AC
2	3	NO	NO
3	4	YES	YES
4	-2	NO	NO
5	-3	NO	NO
6	-4	YES	YES
7	-7	YES	YES
8	-1	NO	NO
9	2	NO	NO
10	7	YES	YES
Sheet3

Cell Formulas
Range	Formula
AB2:AB10	AB2	=IF(OR($AA2<-3,$AA2>3),"YES","NO")
AC2:AC10	AC2	=IF(OR($AA2<-3,ABS($AA2)>3),"YES","NO")

Edited just because of how the XL2BB formula range was showing

 

[MARK858]

Being honest I don't see the difference in results to your original ABS formula (and there shouldn't be a difference either), perhaps it would be better if you posted the results you expect based on the data in the table that I posted then we'll take it from there.

Book1
	AA	AB	AC	AD
1				Original formula
2	3	NO	NO	NO
3	4	YES	YES	YES
4	-2	NO	NO	NO
5	-3	NO	NO	NO
6	-4	YES	YES	YES
7	-7	YES	YES	YES
8	-1	NO	NO	NO
9	2	NO	NO	NO
10	7	YES	YES	YES
Sheet3

Cell Formulas
Range	Formula
AB2:AB10	AB2	=IF(OR($AA2<-3,$AA2>3),"YES","NO")
AC2:AC10	AC2	=IF(OR($AA2<-3,ABS($AA2)>3),"YES","NO")
AD2:AD10	AD2	=IF(ABS(AA2)>3,"YES","NO")

 

[ZochSteveo]

Being honest I don't see the difference in results to your original ABS formula (and there shouldn't be a difference either), perhaps it would be better if you posted the results you expect based on the data in the table that I posted then we'll take it from there.

Book1
	AA	AB	AC	AD
1				Original formula
2	3	NO	NO	NO
3	4	YES	YES	YES
4	-2	NO	NO	NO
5	-3	NO	NO	NO
6	-4	YES	YES	YES
7	-7	YES	YES	YES
8	-1	NO	NO	NO
9	2	NO	NO	NO
10	7	YES	YES	YES
Sheet3

Cell Formulas
Range	Formula
AB2:AB10	AB2	=IF(OR($AA2<-3,$AA2>3),"YES","NO")
AC2:AC10	AC2	=IF(OR($AA2<-3,ABS($AA2)>3),"YES","NO")
AD2:AD10	AD2	=IF(ABS(AA2)>3,"YES","NO")

Ok so basically, I just want to find the outliers using the Z value that are greater than 3, and less than -3. I will attach a screenshot image of what I mean. I am not actually sure how to upload an Excel script onto here like you have done, so I will explain what is going on here. In column A, I have the population of the data that was given to me. In Column D, I have the mean (average) of the population in cell D2, and the standard deviation of the population in D3. In column B, I have used the following formula, and then copied it down: =(A2-$D$2)/$D$3 so it is in column C that I need the help as this will then determine whether or not the data in each row is an outlier or not. And it will be considered an outlier if it is <-3 or >3 so less than -3 or greater than 3.

I hope that makes sense. I will try and work out how to attach an Excel

 

[MARK858]

Can you do what I asked in my previous post and with the table that I posted put your expected results, the results I posted with all the formula give a Yes for the results <-3 or >3 and so I really do not know what you are looking for especially as everything you have shown in column B in your image is >-3 and <3

 

[ZochSteveo]

Can you do what I asked in my previous post and with the table that I posted put your expected results, the results I posted with all the formula give a Yes for the results <-3 or >3 and so I really do not know what you are looking for especially as everything you have shown in column B in your image is >-3 and <3

Perfect, that works. Is there a way to get the cells which are "YES" to highlight a certain colour?