[b]Standard Deviation in CR and Excel[/b]

[Jimdeb03]

I'm attempting to use StdDev with limited success and am wondering if anyone else has come across the same discrepancy between CR's StdDev result and Excel's STDEV result. If I use identical values in both CR and Excel for calculating Standard Deviation, I get different results.

Whatever result Excel calcualates for Standard Dev is considered gospel since our clients MAY use Excel to check our calculations using CR. Unfortunately, CR's results differ from Excel which is unacceptable.

Has anyone else noticed different results as I've mentioned? I'm using nonbiased Standard Deviation in both CR and Excel and am beginning to realize that duplicating results between the two apps may be an exercise in futility; yet they shouldn't be so different as I'm experiencing.

In case anyone's interested, Standard Dev is desired for the following values
100,99.99999,99.99999,99.99999,99.99999,100.00000,100.00000,99.99999,99.99999,99.99999,

Excel Standard Deviation = 0.00000458469553
CR Standard Deviaion = .0000051469

CR code sample below...

    dim ict as number
    Dim nSum2 as number
    Dim nPos as number
    Dim nLen as Number
    dim sSampdat as string
    sSampdat = "100,99.99999,99.99999,99.99999,99.99999,100.00000,100.00000,99.99999,99.99999,99.99999,"
    Dim sSampArray() As number
    ReDim sSampArray(10)

    nPos = 1
    nLen = 0
    
    For iCt = 1 To 10
        sSampArray(iCt) = tonumber(Mid(sSampdat, nPos, InStr(nPos, sSampdat, ",", 1) - nPos))
        nLen = Len(Mid(sSampdat, nPos, InStr(nPos, sSampdat, ",", 1)))' - nPos))
        nPos = nPos + Len(Mid(sSampdat, nPos, InStr(nPos, sSampdat, ",", 1) - nPos))+1
    Next iCt
    
    nSum2 = StdDev(sSampArray)   

formula = nSum2

thanks
Jim

 

[synapsevampire]

Here's the whitepaper, forward it to your client:

http://support.businessobjects.com/library/kbase/articles/c2007430.asp

-k

 

[Jimdeb03]

I'm aware of that article, yet it fails to mention if there is an intrinsic difference between CR and Excel for calculating Standard Dev. Formulas between the two appear similar. I'm going to fathom both are probably correct in their own right for even if Standard Deviation is calculated 'manually' in Excel, i.e. without using the STDEV() function; a different, third result is given.

If my clients weren't well versed with Excel or if they weren't scientists wanting to confirm our findings; we probably could use CR's Standard Deviation and life would go on.

Unfortunately, I'm faced with the prospect my result will be confirmed so it's in my best interest that CR punch out the same result as if it were coming from Excel itself.

So now I'm faced with accessing an Excel spreadsheet for Standard Deviation since Excel is what our clients use and at least the results would be similar.

Is it possible to export data from CR to Excel; calculate Standard Deviation and import the result back into a report?
Jim
Jim

 

[synapsevampire]

That's the long ways there.

What's the original data source? Perhaps you can calculate it there and emulate Excel's answer?

std dev topic:

http://www.maryp.org/excel/stdev.htm

http://inside.salve.edu/walsh/standarddeviationformulas.pdf

Here are some formulas to apply if you're interested, but I would explain to the community that what they're asking for is NOT the real Std Dev.

http://workforce.cup.edu/sweeney/P360_STD_DEV.htm

Or you can create your own Crystal formula to create a bad Std Dev, or perhaps do so on the database.

-k

-k

-k

 

[Jimdeb03]

Thanks for the link reporting the Standard Deviation errors with Excel. It convinced us to calculate Standard Dev 'manually' without using the STDEV() function. Apparently, Excel isn't accurate enough for us when sample values are up to 5 or 6 decimal places.

Unfortunately, my application is already geared for CR to perform these calculations. A string of sample data is extracted into a number array whereupon STDDEV() is performed. Of course it's possible for CR to perform the calculations, provided any calculations isn't rounded along the way.

thanks again