Estimate variance conditionally in Excel?

[declan88]

Hi All

I have two columns.
Column 1: categories 0-4
Column 2: various lengths of time
n=504

Could anyone tell me, how I can calculate the variance in time according to what ever category is picked, in Excel?

Thank you very much

Declan

PS This is my first post

 

[DRSteele]

Re: How can I estimate variance conditionally in Excel?

Welcome to the forum.

I think this might help. I created some sample data and calculated the population variance per category. I used formulas here, but I also know a PivotTable would make quick work of the task.

The formulas in Column C are there to prove the formulas work in Column F. Column A is sorted according to category, but of course it doesn't need to be. Copy F2 downwards after it is properly invoked with Ctrl+Shift+Enter.

	A	B	C	D	E	F
1	category	minutes	Variance		category	Variance
2	0	19			0	26.00
3	0	7			1	480.89
4	0	16	26.00		2	330.67
5	1	59			3	62.67
6	1	9			4	471.84
7	1	51	480.89
8	2	18
9	2	2
10	2	46	330.67
11	3	42
12	3	54
13	3	41
14	3	32
15	3	46
16	3	31	62.67
17	4	38
18	4	56
19	4	21
20	4	2
21	4	60	471.84

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula C4 =VARPA(B2:B4) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=VARPA(IF($A$2:$A$21=E2,$B$2:$B$21))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[declan88]

Re: How can I estimate variance conditionally in Excel?

Thank you very much. An interesting approach with varpa. I shall test is out.

 

[declan88]

Re: How can I estimate variance conditionally in Excel?

Thank you very much. An interesting approach with varpa. I shall test is out.

So I tried and found it didn't work.

Here as example.

Lets say I have a data set with 14 obs.
On the left I show how the data is represent in the table. On the right with the 0s deleted. Underneath this + and - 0s table, I will show the results of different forms of calc.

The different forms of calc of VAR show the 0s effect the outcomes of VAR (labelled simple VAR), VARPA, VARP and VARA.
If VAR -0s gives the correct result, none of the other formulas equal it.
Therefore my original questions remains outstanding:

Does anyone know a mechanism to calculate variance for a sample conditionally to exclude 0s (or blanks, text etc)

Observations Obs minus 0s 0.244 0.244 0.702 0.702 0.571 0.571 1.120 1.120 0.000 0.000 0.286 0.286 0.000 0.429 0.429 0.000 0.000 0.286 0.286 0.571 0.571 0.000 <colgroup><col><col></colgroup><tbody> </tbody> With 0s Without 0s Mean (-0s) 0.526 0.526 Simple var 0.119 0.085 VARPA 0.110 0.074 VARP 0.113 0.063 VARA 0.112 0.095 {=VARPA(IF(… 0.074 0.074 <colgroup><col style="mso-width-source:userset;mso-width-alt:3612;width:76pt" width="102"> <col style="width:48pt" width="64" span="2"> </colgroup><tbody> </tbody> <colgroup><col><col span="2"></colgroup><tbody> </tbody>

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

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[declan88]

Re: How can I estimate variance conditionally in Excel?

I worked it now.

The fomula for conditional variance is:
conditional STDEV^2

Viz

{=STDEV(IF(RANGE X1:Xn<>0;X1:Xn))*STDEV(IF(X1:Xn<>0; X1:Xn))} cntrl shift enter for curly brackets
or
{=(STDEV(IF(X1:Xn<>0; X1:Xn)))^2} cntrl shift enter for curly brackets