Getting different result copying formula from Excel to ASP

Question

I'm trying to replicate a formula from an Excel worksheet onto an ASP page, but having copied the formula over I'm getting different results (in fact an error because the ASP ends up trying to square root a negative number).

Surely Excel handles operator precedence the same as classic ASP?

The formula I'm trying to replicate is a simple "T-Test" (to report on statistical significance - I didn't create the formula) and works correctly in Excel. In Excel, the cells look like

A2 = 1098
B2 = 183

A4 = 20.4
B4 = 17.49

And cell E4 contains this formula:

=(A4-B4)/SQRT(((($A$2*A4)+($B$2*B4))/($A$2+$B$2))*
(1-((($A$2*A4)+($B$2*B4))/($A$2+$B$2)))*((1/$A$2)+(1/$B$2)))

So, I simply copy/pasted that formula into classic ASP, removed all the $ and changed SQRT to SQR, declared 4 variables A2, B2, A4, B4. That returns an error (invalid procedure call).

If I remove the first part of the formula - (A4-B4)/SQRT - in ASP the code returns -2.41868099141296, yet in Excel it's returning 0.001019435.

I have tried and tried calculating parts of the formula separately first, and then putting it back together ready to do the SQRT but keep facing the same thing.

Is this just a silly mistake? At one point I was looking into square roots of negative numbers until I tried =SQRT(-9) in Excel

ASP Code I'm using is (I've inserted a line break on the first and last line):

A2 = cdbl(1098.0)
A4 = cdbl(20.4)
B2 = cdbl(183.0)
B4 =cdbl(17.49)

' Remove all the $, and (A4-B4)/SQRT part
response.write "<p>Result: " & ((((A2*A4)+(B2*B4))/(A2+B2))*(1-(((A2*A4)+(B2*B4))
/(A2+B2)))*((1/A2)+(1/B2))) & "</p>"`

Answer 1

In your Excel sheet, are you doing any input validation that's preventing you from getting the root of a negative number?

Also, could you post the relevant ASP code? It could just be a simple transcription error...

Answer 2

I think it's a case of Excel always using floats and ASP using integers (f.i. 1/A2 - which is an integer - might produce different results.

Try using cDbl( ... ) around integer calculations.

Answer 3

I'm a dufus.

In the Excel spreadsheet columns A4 and B4 are formatted as percentage. The actual value in these cells are 0.204 and 0.1749 respectively. The Excel formula does work when copied and pasted (almost) literally into ASP - I just didn't catch that my inputs were 100x larger than expected.

The following code works

A2 = cdbl(1098.0)
A4 = cdbl(0.204)
B2 = cdbl(183.0)
B4 =cdbl(0.1749)

response.write "<p>Result: " & (A4-B4)/SQR((((A2*A4)+(B2*B4))/(A2+B2))*
(1-(((A2*A4)+(B2*B4))/(A2+B2)))*((1/A2)+(1/B2))) & "</p>"

So now I just need to convert that into a function.