STDEV Excel To PHP differences in decimals

I'm trying to do STDEV Excel's function in PHP, but I get different results , maybe it's just something related with decimals or rounding, because results are veeeeeeery similar, but when I try to get exactly same number I got on excel... I just can´t

I tried...

        $nums = [1004, 1005, 1004];

        $sum   = 0;

        for($i = 0; $i < count($nums); $i++)
        {
            $sum += $nums[$i];
        }


        $media = ($sum / count($nums));


        $sum2  = 0;

        for($i = 0 ; $i < count($nums); $i++)
        {
            $sum2+=($nums[$i]-$media)*($nums[$i]-$media);
        }

        $vari     = ($sum2 / (count($nums) - 1));
        $devStd   = sqrt($vari);

        echo $devStd; // results 0.57735026918963

I get 0.57735026918963 in PHP, but with same values using Excel I get 0.57735026918963400000, and I know what you're thinking "just use number_format() or round()", but even If a do something like...

$devStd = number_format($devStd, 18, '.', false); // then I get 57735026918962573106

I get 57735026918962573106 instead 0.57735026918963400000

And If I use round, y just get 0.57735026918963 no matter what precision param

thanks

The correct analytic answer by inspection should be 1/sqrt(3).

However, 1/3 is one of the numbers that IEEE 754 floating point binary cannot accurately represent and your computation of the mean and sum of squares is imperfect.

 sqrt(3) = 5.77350269189625764509148780502e-1 (to 20 dp) from Maxima

I have put a gap in the OP's quoted numbers to indicate where they deviate from the reference correct value above.

The OP's answers are:

"I get 0.5773502691896 3 in PHP,"

That format hasn't got enough decimal places to display a 64 bit double unambiguously. It needs 16 or preferably 18 decimal digits to be shown. I suspect the "3" displayed by PHP is rounded up from "2576" or "258" in which case it has performed admirably given IEEE754 double limitations.

 but with same values using 
 "Excel I get 0.5773502691896 3400000" (sic)

The trailing zeroes are incredibly misleading. Excel hides details under the bonnet by truncating to 0 after 15 digits displayed. You can force it to display the true value by manually subtracting off a few leading digits.

The STDEV() in the Excel version you have is defective do not use it as a reference!

Excel actually gets sqrt(3) to be 0.57735026918962625 868 and so does the version of STDEV() in my copy of XL2021 when applied to 1004,1005,1004.

The OP doesn't say which version of Excel he is using but with my copy of Excel 2021 STDEV() gives the right answer on this test to machine precision. It also gets the same right answer on 1000004,1000005,1000004 (which is a stiffer test for algorithmic weaknesses).

If you want to do even better that what you have now in really demanding situations then look up and implement Kahan summation which allows some compensation to be made for rounding errors when summing floating point numbers in the real world. Beware that some optimisers can trash it.