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

	A	B	C	D
1	Name	Number
2	A	1
3	B	5
4	C	7
5	A	2
6	E	3
7	F	8
8	G	9
9	A	6
10
11
12				2.645751
13
14				2.645751

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D14 =STDEV(B2,B5,B9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D12 {=STDEV(IF($A$2:$A$9=A9,$B$2:$B$9))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[mayoman51]

Data A	Data B
	1
	1
	1.5
	0.25
1	0.5
1.25	2
0.5	-0.5
-.5	-1
-1	-.5
0.25	1.5	1.21106

<tbody>
</tbody>

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
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula C10 {=STDEV(IF($A$1:$A$10<>"",$B$1:$B$10))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you, this worked

 

[AlanY]

you're welcome