Removing outliers from being counted in Average/Standard deviation functions??

[Tycoons]

Hi,

As a sciences undergrad, I have a number of massive lists of data that I need to sort through... Some of the averages and standard deviations are getting massively skewed as a result of outlier data.. Is there a way I can exclude these from my =St.dev or =avg function without having to manually remove them?

One my lists for example, is being sorted as
=STDEV.S(IF($B$13:$B$322="Bacteria Size",I$13:I$322))... is there any way I can incorporate some sort of filter that will exclude massive outliers (Both (+) and (-)) from that equation?

Really appreciate any advice. Thanks.

 

[AhoyNC]

Assuming you consider an outlier to be +/- 1.5 times the IQR, then maybe something like below.

Use the QUARTILE function to calculate the 3rd and 1st quartiles.
Subtract the 2 to get your interquartile range (IQR)
Use this to calculate the Upper and Lower bounds.

You can then use the AVERAGEIFS function.
The standard deviation formula in cell D10 below is an array function and must be entered with CTRL-SHIFT-ENTER.

You might also wnt to look at the TRIMMEAN function.

Excel Workbook
	A	B	C	D
1	Data
2	45		3rd Qt.	38
3	21		1st Qt	25
4	25		Interquartile Range	13
5	101
6	36		Upper Bound	57.5
7	-12		Lower Bound	5.5
8	35
9	38		Average	32.571
10	28		Standard Dev.	8.3038
Sheet

 

[Tycoons]

I think the Quartile function works great, but I can't seem to get it to work if I also try to filter out the data that I'm trying to average (I have bacteria size, Virus size etc)..... for example

=Quartile(IF($B$13:$B$322="Bacteria Size",I$13:I$322),3)
=Quartile(IF($B$13:$B$322="Bacteria Size",I$13:I$322),1)

But it returns a #Value!... what do I need to do to make this work?

Thanks again!

 

[Tycoons]

Oh jesus... forgot to ctrl+shift+enter.. woops.

Thanks a lot.

 

[Tycoons]

Assuming you consider an outlier to be +/- 1.5 times the IQR, then maybe something like below.

Use the QUARTILE function to calculate the 3rd and 1st quartiles.
Subtract the 2 to get your interquartile range (IQR)
Use this to calculate the Upper and Lower bounds.

You can then use the AVERAGEIFS function.
The standard deviation formula in cell D10 below is an array function and must be entered with CTRL-SHIFT-ENTER.

You might also wnt to look at the TRIMMEAN function.

*	A	B	C	D
*	*	*
*	3rd Qt.
*	1st Qt
*	Interquartile Range
*	*	*
*	Upper Bound
*	Lower Bound
*	*	*
*	Average
*	Standard Dev.

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:22px;"><col style="width:135px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Data[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]45[/TD]

[TD="align: right"]38[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]21[/TD]

[TD="align: right"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]25[/TD]

[TD="align: right"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]101[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]36[/TD]

[TD="align: right"]57.5[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]-12[/TD]

[TD="align: right"]5.5[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]38[/TD]

[TD="align: right"]32.571[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]28[/TD]

[TD="align: right"]8.3038[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =QUARTILE(A2:A10,3) D3 =QUARTILE(A2:A10,1) D4 =D2-D3 D6 =D2+1.5*D4 D7 =D3-1.5*D4 D9 =AVERAGEIFS(A2:A10,A2:A10,"<="&D6,A2:A10,">="&D7) D10 {=STDEV.S(IF(A2:A10<=D6,IF(A2:A10>=D7,A2:A10)))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

So I've got the interquartile range all sorted...

But my Stdev and AVG formula is a mess.. any chance you can help me fix this up?

=AVERAGEIFS($B$13:$B$322="Bacteria Size",I$13:I$322,if($B$13:$B$322,$B$13:$B$322,,"<="&D6,A2:A10,">="&D7))

{=STDEV.S(IF$B$13:$B$322="Bacteria Size",I$13:I$322,if($B$13:$B$322,$B$13:$B$322,,"<="&D6,A2:A10,">="&D7))

 

[AhoyNC]

See if this helps (Note: AVERAGEIFS function is in Excel 2010 and later, if you have an earlier ver. of Excel we will need to use an array function).

Also, the Standard Dev. formula is an array and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2			3rd Qt.	38
3			1st Qt	25
4			Interquartile Range	13
5
6			Upper Bound	57.5
7			Lower Bound	5.5
8
9			Average	32.571
10			Standard Dev.	8.3038
11
12				32.571					Data
13		Bacteria Size							45
14		test							100
15		Bacteria Size							21
16		test							200
17		test							300
18		Bacteria Size							25
19		Bacteria Size							101
20		Bacteria Size							36
21		Bacteria Size							-12
22		Bacteria Size							35
23		Bacteria Size							38
24		Bacteria Size							28
Sheet

 

[Tycoons]

Everything works now, finished product is great. Unbelievable how helpful you were. Thanks a lot.

 

[AhoyNC]

You're welcome. Thanks for the feedback.