Data Cleaning

[ZochSteveo]

Hi everyone.

I was wandering if anyone could help me. I am currently working on a very large dataset involving temperatures and relative humidity. It is for my dissertation at university. As a part of the process I need to clean the data.

I have two options for this. I am going to attempt to clean the data by finding the average of the list of values, as well as its standard deviation. And then any numbers that are 2 times the standard deviation away from the average I am going to consider as outliers.

Please could someone advise me on how I could write a formula for every cell that will check if the value is two times the standard deviation away from the average. Or advise another suggestion. I will try and attach a photo to show what it is that I am dealing with, in order for it to make more sense.

 

[myall_blues]

Try something like

=IF(OR(C2>$F$3+2*$F$2,C2<$F$3-2*$F$2),TRUE,"")

where:
C2 contains the first temperature value
$F$2 contains the SD
$F$3 contains the mean

and copy the formula down for each value in column C. Anything that comes up TRUE is outside the +/-2SD range.

 

[ZochSteveo]

Try something like

=IF(OR(C2>$F$3+2*$F$2,C2<$F$3-2*$F$2),TRUE,"")

where:
C2 contains the first temperature value
$F$2 contains the SD
$F$3 contains the mean

and copy the formula down for each value in column C. Anything that comes up TRUE is outside the +/-2SD range.

Hi there, thank you for this. Is the formula meant to go in an adjacent cell, say for example D2, which I can then copy down for each of the corresponding data values?

 

[myall_blues]

Yes that’s correct.