Hi all I am a uni student doing a thesis project on timber.
I have a lot of data to go through. I need to average the data and not include outliners which are based on 2 standard deviations away from the mean positive and negative at the same time.
Here is a very small extract of my data:
1/09/2013 1:44 -660
1/09/2013 1:45 10064
1/09/2013 1:46 -665
1/09/2013 1:47 -666
1/09/2013 1:48 -66666
1/09/2013 1:49 -669
1/09/2013 1:50 -668
1/09/2013 1:51 -669
The formula I use at the moment which includes the outliners is :
=((AVERAGEIFS(September!V:V,September!A:A,">15/09/2013 00:00",September!A:A,"<15/09/2013 23:59"))+110.6)*2/2.12
So the raw data is on a sheet called September and the Processed data is on Sheet 1.
I use this formula because it helps average data between specific dates/times but need help on not including outliners (based on 2 std away from mean).
The +110.6 is the reference point for the data and the 2/2.12 is a modification factor.
Any help is greatly appreciated!
Thanks
I have a lot of data to go through. I need to average the data and not include outliners which are based on 2 standard deviations away from the mean positive and negative at the same time.
Here is a very small extract of my data:
1/09/2013 1:44 -660
1/09/2013 1:45 10064
1/09/2013 1:46 -665
1/09/2013 1:47 -666
1/09/2013 1:48 -66666
1/09/2013 1:49 -669
1/09/2013 1:50 -668
1/09/2013 1:51 -669
The formula I use at the moment which includes the outliners is :
=((AVERAGEIFS(September!V:V,September!A:A,">15/09/2013 00:00",September!A:A,"<15/09/2013 23:59"))+110.6)*2/2.12
So the raw data is on a sheet called September and the Processed data is on Sheet 1.
I use this formula because it helps average data between specific dates/times but need help on not including outliners (based on 2 std away from mean).
The +110.6 is the reference point for the data and the 2/2.12 is a modification factor.
Any help is greatly appreciated!
Thanks
Last edited: