Variance and STD Deviation - Not consistent

[shammi_Raj]

Dear Friends,

Thank you in advance for looking into and replying.

I am trying to make sense of VAR and STDDEV in Excel.

below are two cases .. case 1: STDDEV and VAR is same when I calculate with excel formulas or mathematically
Case 2: The numbers are different when I use excel Formulas and calculate manually..

Please let me know what am doing wrong. Attached also the file for easy reading (attachments functionality not working).


Case 1

Payoff P P*payoff

700 0.5 350
1400 0.5 700

E Return 1050

STDDEV = STDDEV (350,700,1050) = 350 - Same when calculate manually with formulas


Case 2
PayOff P P*payoff
100 0.1 10
700 0.4 280
1400 0.4 560
2000 0.1 200

E Return 1050

STDDEV = STDDEV(10,280,560,200,1050) = 404 -> When calculate with formulas manually - STDDEV = 528

[davsth]

I think your manual calculation is in error, as I get exactly the same result as excel working it out manually. given you provide no workings or interim stages, its difficult to say more
ave=420
Sum(x-ave)squared =652600
N=4

SD =SQRT(652600/4)=403.91

[José Augusto]

Hi @shammi_Raj
What formula are you using to get the STDDEV manually ?
Excel has right.
Another note. Why do you include 1050 which is the sum of the values?