Standard Deviation Formula 1

[victora]

Hello All,
How can I conditionally calculate the Standard Deviation of a series of values, i.e. include in the calculation of StdDev if less than 50.

My existing formula is shown:
StdDev ({@ERVisitsPer1K})

Thank you

 

[dgillz]

Please post the formula for {@ERVisitsPer1K}

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

I think you'd have to do this in a series of steps. First, you would create two conditional formulas:

//{@amt<50}:
if {table.amt} < 50 then {table.amt} else 0

//{@cntamt<50}:
if {@amt<50} <> 0 then 1 else 0

Then create a formula:

//{@detailcalc} to be placed in the detail section:
whileprintingrecords;
numbervar ave := sum({@amt<50})/sum({@cntamt<50);
numbervar diffsq := if {@amt<50} <> 0 then
({@amt<50} - ave)^2;
numbervar adddiffsq := adddiffsq + diffsq;

//{@stddev} to be placed in the report footer:
whileprintingrecords;
numbervar adddiffsq;
sqr(adddiffsq/(sum({@cntamt<50)-1))

-LB

 

[Madawc]

Running totals include 'Sample Standard Deviation' as an option. I've never used it, but it may suit your needs.

Madawc Williams (East Anglia, UK)

 

[victora]

Thanks LB. As usual, your solution is correct! To check calculation, I exported the report to Excel and calculate the StdDev using the excel built-in function. Theres a slight difference. Pls see below: (Pls take note, I wrongly mentioned in my my posting < 50. Actually, I need to calculate StdDev for values > 50 only. In the example below, the last 4 doctors will not be included)

Values
Doctor Name1 331.79
Doctor Name2 208.96
Doctor Name3 193.80
Doctor Name4 180.61
Doctor Name5 173.44
Doctor Name6 167.29
Doctor Name7 139.60
Doctor Name8 134.66
Doctor Name9 126.44
Doctor Name10 120.63
Doctor Name11 116.28
Doctor Name12 114.70
Doctor Name13 96.33
Doctor Name14 95.89
Doctor Name15 80.59
Doctor Name16 72.21
Doctor Name17 69.12
Doctor Name18 67.27
Doctor Name19 64.55
Doctor Name20 64.37
Doctor Name21 61.68
Doctor Name22 53.88
Doctor Name23 38.26
Doctor Name24 33.25
Doctor Name25 0.00
Doctor Name26 0.00


Standard Deviation = 66.66

Standard Deviation (using excel built-in function)= 66.03


Thanks

 

[victora]

madawc,
Thanks for your input. Yes, there is a StdDev function but I cannot find a way to use it conditionally. Pls see sample values in my previous posting.

Thanks

 

[lbass]

I'm not sure I can explain this. I wonder whether it is a rounding error. You might try changing the decimals and rounding settings in CR to increase precision and see if that makes a difference.

-LB