Standard Deviation if formula

[mayoman51]

Hi,

I'm trying to compare the standard deviations 2 different data sets A & B over a period of time. The sets of data do not begin at the same time however, so I'm looking for a formula that will exclude data from the standard deviation calculation of B where there is a blank value for the data at the corresponding time in A.
IS there an IF function that can do this?

 

[sheetspread]

Look here:

http://www.mrexcel.com/forum/excel-...verage-std-dev-max-min-using-index-match.html

 

[mayoman51]

I tried to adapt the formula to work =STDEV(IF('Sheet 1'!$B$3:$B$2611="<>",'Sheet 2'!$B$4:$B$2612))
I'm getting a #DIV/0! error, I've tried different values instead of the "<>" but they all give the same error

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E
1
2	2
3	2		8
4	9		2
5	22		22
6	4		1
7	5		4
8	54		56
9	3		3		19.62396
10	7		3
11			19		19.62396
Sheet18

Cell Formulas
Range	Formula
E11	=STDEV(A2,A4,A5,A7,A8,A10)
E9	{=STDEV(IF($A$2:$A$10<>$C$3:$C$11,$A$2:$A$10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mayoman51]

No I tried this formula, i'll try the above now


Excel 2010

Name	Number
A
B
C
A
E
F
G
A

<colgroup><col><col><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

[TD="align: center"]3[/TD]

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

[TD="align: center"]4[/TD]

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

[TD="align: center"]5[/TD]

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

[TD="align: center"]6[/TD]

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

[TD="align: center"]7[/TD]

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

[TD="align: center"]8[/TD]

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

[TD="align: center"]9[/TD]

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

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.645751[/TD]

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

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.645751[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px, align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5, align: center"]D14[/TH]
[TD]=STDEV(B2,B5,B9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px, align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5, align: center"]D12[/TH]
[TD]{=STDEV(IF($A$2:$A$9=A9,$B$2:$B$9))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

[/TD]
[/TR]
</tbody>[/TABLE]

 

[mayoman51]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Data A[/TD]
[TD]Data B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.25[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[TD]-0.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-.5[/TD]
[TD]-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]-.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.25[/TD]
[TD]1.5[/TD]
[TD]1.21106[/TD]
[/TR]
</tbody>[/TABLE]


I still can't get it to work. Maybe im not entering it properly.
In the example above, for column B, I only want to include values in the standard deviation calculation when there is a corresponding value in column A. So in this example the first 4 rows would be excluded from the standard deviation calculations, resulting in a stdev of 1.21106 for the remaining 6 entries

 

[AlanY]

this gives the same answer as yours

Excel 2012
	A	B	C
1		1
2		1
3		1.5
4		0.25
5	1	0.5
6	1.25	2
7	0.5	-0.5
8	-0.5	-1
9	-1	-0.5
10	0.25	1.5	1.21106
Sheet5

Cell Formulas
Range	Formula
C10	{=STDEV(IF($A$1:$A$10<>"",$B$1:$B$10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mayoman51]

this gives the same answer as yours

Excel 2012

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]1.5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1.21106[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]{=STDEV(IF($A$1:$A$10<>"",$B$1:$B$10))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you, this worked

 

[AlanY]

you're welcome