Data Cleaning

ZochSteveo

New Member
Joined
Dec 21, 2024
Messages
18
Office Version
  1. 2024
Platform
  1. MacOS
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.
 

Attachments

  • Screenshot 2025-02-07 at 15.09.27.png
    Screenshot 2025-02-07 at 15.09.27.png
    60.1 KB · Views: 13
Try something like
Excel Formula:
=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.
 
Upvote 0
Try something like
Excel Formula:
=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?
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top