Standard Deviation

[pagrender]

How do I calculate which of this data is outside the standard deviation?

Excel Workbook
	A
1	1.040
2	1.078
3	1.052
4	1.003
5	1.028
6	1.008
7	1.077
8	1.036
9	0.972
Sheet1

 

[VoG]

Maybe like this

Excel Workbook
	A	B	C	D
1	1.04		SD	TRUE
2	1.078		0.034731	FALSE
3	1.052		MEAN	TRUE
4	1.003		1.032667	TRUE
5	1.028			TRUE
6	1.008			TRUE
7	1.077			FALSE
8	1.036			TRUE
9	0.972			FALSE
Sheet4

 

[pagrender]

Thanks Vog. That worked great.

One follow-up question... Is there a formula I can use that will give me the average of all the data that lies within the standard deviation?

I can alter your formulas in column D (listed below) to get me the answer... but I thought you could do it with one formula instead of the ten it would take me.

Thanks!
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1.04</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">1.052</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1.003</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">1.028</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">1.008</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">1.036</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">1.027833</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=IF(ABS(A1-$C$4)<$C$2,A1,"")</TD></TR><TR><TD>D2</TD><TD>=IF(ABS(A2-$C$4)<$C$2,A2,"")</TD></TR><TR><TD>D3</TD><TD>=IF(ABS(A3-$C$4)<$C$2,A3,"")</TD></TR><TR><TD>D4</TD><TD>=IF(ABS(A4-$C$4)<$C$2,A4,"")</TD></TR><TR><TD>D5</TD><TD>=IF(ABS(A5-$C$4)<$C$2,A5,"")</TD></TR><TR><TD>D6</TD><TD>=IF(ABS(A6-$C$4)<$C$2,A6,"")</TD></TR><TR><TD>D7</TD><TD>=IF(ABS(A7-$C$4)<$C$2,A7,"")</TD></TR><TR><TD>D8</TD><TD>=IF(ABS(A8-$C$4)<$C$2,A8,"")</TD></TR><TR><TD>D9</TD><TD>=IF(ABS(A9-$C$4)<$C$2,A9,"")</TD></TR><TR><TD>D10</TD><TD>=AVERAGEIF(D1:D9,">0")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[shg]

       --A-- --B-- -----------------------C-----------------------
   1   0.345 0.372 B1: =AVERAGE(A1:A10)                           
   2   0.076 0.305 B2: =STDEV(A1:A10)                             
   3   0.042 0.189 B3: {=AVERAGE(IF(ABS(A1:A9 - B1) < B2, A1:A9))}
   4   0.693                                                      
   5   0.084                                                      
   6   0.295                                                      
   7   0.732                                                      
   8   0.143                                                      
   9   0.882                                                      
  10   0.432

The formula in B3 must be confirmed with Ctrl+Shift+Enter; that will make the curly braces appear.

 

[pagrender]

That did the trick! Thanks!

 

[VoG]

Or, CSE entered

=AVERAGE(IF(ABS(A1:A9-AVERAGE(A1:A9)) < STDEV(A1:A9),A1:A9))<stdev(a1:a9),a1:a9))></stdev(a1:a9),a1:a9))>