CODEWITHSUNDEEP
sqlexcelsql-server-2008mathexcel-2010
Haminteu
Haminteu

Reputation: 1334

Different calculation in SQL Server and Excel

This is my formula. I put it in a SQL Server stored procedure:

DECLARE @Var01 float
SET @Var01 = 1164.83 * (1 - 3.3387306 * LOG(0.00459418151829729) + 1.426559 * POWER(LOG(0.00459418151829729),2)) / (1 - 3.4680733 * LOG(0.00459418151829729) + 1.8779192 * POWER(LOG(0.00459418151829729), 2) - 0.21223784 * POWER(LOG(0.00459418151829729), 3) - 0.0035814371 * POWER(LOG(0.00459418151829729), 4) - 0.90903163 * POWER(10, -4) * POWER(LOG(0.00459418151829729), 5)) - 459.67

The result is: 214.630185149416

Then I'm trying to compare to excel, the formula as below:

=1164.83 * (1 - 3.3387306 * LN(0.00459418151829729) + 1.426559 * (LN(0.00459418151829729)) ^ 2) / (1 - 3.4680733 * LN(0.00459418151829729) + 1.8779192 * (LN(0.00459418151829729)) ^ 2 - 0.21223784 * (LN(0.00459418151829729)) ^ 3 - 0.0035814371 * (LN(0.00459418151829729)) ^ 4 - 0.90903163 * 10 ^ -4 * (LN(0.00459418151829729)) ^ 5) - 459.67

The result is: 211.981432072480

The question is, which one is correct? Any Idea? What the calculation is different?

Upvotes: 1

Views: 2366

Answers (1)

Douglas Zare
Douglas Zare

Reputation: 3316

The comments have speculated that this is roundoff error, and that SQL Server is more reliable because it uses more precise floats than Excel. This is wrong. The relative error is about 1%. You do not get relative errors of 1% when you perform a short computation while making roundoff errors of 10-13% unless you are subtracting nearly equal large numbers.

I suggested breaking down the computation to see if SQL Server and Excel agree on the pieces, to see where they diverged. This would have worked. It's like stepping through a program instead of just saying that the end result is not what was desired. You can do a binary search to find the problem rapidly, but the OP didn't provide any additional information.

The computation being performed is

1164.83\frac{1+17.9723+41.3364}{1+18.6685+54.4152+33.1045-3.00707+0.410853} - 459.67

= 1164.83 \frac{60.3087}{104.592}-459.67

=211.981

There isn't any cancelation of huge numbers that might cause a large relative error. So, I tried to solve

1164.83 \frac{60.3087}{104.592+x}-459.67 = 214.630185149416

to see what error in the denominator would result in this miscalculation. With a little calculus, I could check for typos in the constants. The solution is x=-0.410829. That's almost exactly the last term in the denominator. So, the answer isn't that one of these environments produces 1% relative errors in simple floating point calculations, it's that a term was dropped in the denominator. This would have been obvious from breaking the calculation into pieces.

The last term is the only one with something like POWER(10,-4). Could it be that this is carried out using integer arithmetic instead of floating point, so that it evaluates to 0 instead of 0.0001? Yes, apparently that's what SQL Server does. It's like 1/2=0 in integer arithmetic. If you want a decimal output you have to give it a decimal input. Cast the 10 to decimal, change it to POWER(10.0,-4), use 0.0001, or use proper scientific notation for the whole coefficient.

Upvotes: 4

Related Questions