Estimate variance conditionally in Excel?

D

declan88

New Member
Joined
Apr 20, 2017
Messages
5
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.

ABCDEF

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]category[/TD]
[TD="bgcolor: #FFF2CC"]minutes[/TD]
[TD="bgcolor: #FFF2CC"]Variance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]category[/TD]
[TD="bgcolor: #FCE4D6"]Variance[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]26.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #C6E0B4, align: right"]480.89[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16[/TD]
[TD="bgcolor: #E2EFDA, align: right"]26.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #C6E0B4, align: right"]330.67[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #C6E0B4, align: right"]62.67[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #C6E0B4, align: right"]471.84[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]51[/TD]
[TD="bgcolor: #E2EFDA, align: right"]480.89[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]46[/TD]
[TD="bgcolor: #E2EFDA, align: right"]330.67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]42[/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"]3[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]41[/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"]3[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]31[/TD]
[TD="bgcolor: #E2EFDA, align: right"]62.67[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]60[/TD]
[TD="bgcolor: #E2EFDA, align: right"]471.84[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=VARPA(B2:B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=VARPA(IF($A$2:$A$21=E2,$B$2:$B$21))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Re: How can I estimate variance conditionally in Excel?

Thank you very much. An interesting approach with varpa. I shall test is out.
 
Upvote 0
Re: How can I estimate variance conditionally in Excel?

declan88 said:
Thank you very much. An interesting approach with varpa. I shall test is out.
Click to expand...

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)

[TABLE="width: 152"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 152"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Observations[/TD]
[TD]Obs minus 0s[/TD]
[/TR]
[TR]
[TD="align: right"]0.244[/TD]
[TD="align: right"]0.244[/TD]
[/TR]
[TR]
[TD="align: right"]0.702[/TD]
[TD="align: right"]0.702[/TD]
[/TR]
[TR]
[TD="align: right"]0.571[/TD]
[TD="align: right"]0.571[/TD]
[/TR]
[TR]
[TD="align: right"]1.120[/TD]
[TD="align: right"]1.120[/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.286[/TD]
[TD="align: right"]0.286[/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.429[/TD]
[TD="align: right"]0.429[/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.286[/TD]
[TD="align: right"]0.286[/TD]
[/TR]
[TR]
[TD="align: right"]0.571[/TD]
[TD="align: right"]0.571[/TD]
[/TR]
[TR]
[TD="align: right"]0.000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Mean (-0s)
Simple var
VARPA
VARP
VARA
{=VARPA(IF(…

<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>
[TD="width: 102"][/TD]
[TD="class: xl65, width: 64"]With 0s[/TD]
[TD="class: xl65, width: 64"]Without 0s[/TD]

[TD="class: xl67, align: right"]0.526[/TD]
[TD="class: xl67, align: right"]0.526[/TD]

[TD="class: xl66, align: right"]0.119[/TD]
[TD="class: xl68, align: right"]0.085[/TD]

[TD="class: xl66, align: right"]0.110[/TD]
[TD="class: xl66, align: right"]0.074[/TD]

[TD="class: xl66, align: right"]0.113[/TD]
[TD="class: xl66, align: right"]0.063[/TD]

[TD="class: xl66, align: right"]0.112[/TD]
[TD="class: xl66, align: right"]0.095[/TD]

[TD="class: xl67, align: right"]0.074[/TD]
[TD="class: xl67, align: right"]0.074[/TD]

</tbody>
[TABLE="width: 230"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 230"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
520
Fluff
Fluff
M
  • Question Question
Conditional formatting on revenue variances
Replies
8
Views
1K
awoohaw
awoohaw
W
  • Question Question
Use data from cells of one worksheet table to determine table & column selection in a different worksheet.
2
Replies
10
Views
608
Worksong
W
wizau
  • Question Question
Shirt Size with 2x Conditions (Chest & Height)
Replies
8
Views
193
Peter_SSs
Peter_SSs
G
  • Question Question
Links to cells in other Workbooks occasionally change the WORKSHEET reference. Why?
2
Replies
11
Views
1K
GTS
G

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top