View Full Version : Calculation error, different result from excel

Furton

07-13-2004, 01:11 PM

I'm having difficulty with some calculation I'm trying to achieve in javascript

I'm converting this Excel formula

=E15*12/1.25/5.15*100 (where E15 is a random, say 550) and the answer is 102524.2718.

When I converted this into a java script calculation I did this.

var maxMortgage65 = Math.ceil(CurRent * 12 / 1.25 / payRate65 * 100);

(payRate in this example would be 5.15)

But this gave an answer of 10252500, which is wrong.

So I've tried to rail the calculation to force it to do it in order

var temp = Math.ceil(CurRent * 12);

var temp2 = Math.ceil(temp / 1.25);

var temp3 = Math.ceil(temp2 / payRate65);

var temp4 = Math.ceil(temp3 * 100);

var maxMortgage65 = Math.ceil(temp4);

Which gave the same answer, 10252500. Why is javascript doing this? I've tried it without the Math.ceil method but that made no difference (except adding numbers after the decimal point). Any ideas?

for me

Math.ceil(550*12/1.25/5.5*100)

gives

96000

:D

Furton

07-13-2004, 01:23 PM

was that in excel or in a calculator? If in a calculator then you need to press equals after every action. Any ideas?

glenngv

07-13-2004, 01:32 PM

Try putting parentheses to specify the intended precedence.

Math.ceil( ( (CurRent * 12) / 1.25 ) / (payRate65 * 100) );

Is that what you intended to do?

Try putting parentheses to specify the intended precedence.

It's the same thing, as the * and / are fully associative operators one to eachother

Furton

07-13-2004, 01:55 PM

It's what I'm trying to achieve, but never seems to work, I either get the result

10252428 or 1026 (from glenngv)

I can never get the same answer as excel, which is 102524.2718 (Not fussed about the numbers after the decimal.

I've been trying for hours to get this to work correctly. Even tried dividing the answer by 100 to get rid of the last two digits but that doesn't work for all figures.

glenngv

07-13-2004, 01:59 PM

It's the same thing, as the * and / are fully associative operators one to eachother

These give 2 different answers when I run it in the address bar. My browser is Firefox.

javascript:alert(Math.ceil(550 * 12 / 1.25 / 5.15 * 100));alert(Math.ceil(((550 * 12) / 1.25) / (5.15 * 100)))

The first alert gives 102525, 2nd gives 11.

I reckon I don't quite well understand what that EXCEL formula does. As I repeat, when I operate (javascript or even a simple calculator) the operation:

550 'multiplied with' 12 'divided by' 1.25 'divided by' 5.5 'multiplied with' 100

the result is obviously 96000

So can you translate the Excel calculation in a simple linear math calculation?

glenngv

07-13-2004, 02:06 PM

It's what I'm trying to achieve, but never seems to work, I either get the result

10252428 or 1026 (from glenngv)

I can never get the same answer as excel, which is 102524.2718 (Not fussed about the numbers after the decimal.

I've been trying for hours to get this to work correctly. Even tried dividing the answer by 100 to get rid of the last two digits but that doesn't work for all figures.

As you see on my previous post, your code works for me. It gave me 102525. There's no decimal point as it was rounded up because of the Math.ceil method. Removing it will give you 102524.2718446602, which is close to what Excel produces.

Furton

07-13-2004, 02:16 PM

Got it working, thanks all:D

Think I might be using this forum more often now :thumbsup:

glenngv

07-13-2004, 02:17 PM

My test results show that division takes precedence over multiplication.

In essence, this:

alert(Math.ceil(550 * 12 / 1.25 / 5.15 * 100));

is the same as:

alert(Math.ceil(550 * (12 / 1.25 / 5.15) * 100));

Both gives 102525.

So my original suggestion of putting parentheses to specify the intended operator precedence will make the operations clearer and get the expected result.

glenngv

07-13-2004, 02:18 PM

Got it working, thanks all:D

Think I might be using this forum more often now :thumbsup:

For reference, could you post your solution?

I have now the same results with glenn (I copied wrong a value). Tested with IE6, Firefox, Opera7.5, NS6, NS7

1. to Furton : how's possible to get 10252428 or 1026 ?

2. to glenn: that is weird... Are there classical math associative and distributive properties not respected by javascript operators?

Furton

07-13-2004, 02:42 PM

Along with the calculation being in-correct I was using values that work for some percentage calculations and not for others.

The payRate var was calculated from two other var's that were 0.0045 and 0.0065, this worked great for doing a simple figure*payRate to discover the percentage but it didn't work correctly in the above calculation. So I changed the var's that calculated pay rate to simply 4.5 and 0.65 which fixed the problem and declared new var's for anything figure*payRate.

This was really a problem created by some sloppy programming by me (I think), but glad it's all done now. Do you want the whole code posted here or in the "post a java script" forum?

Once, appricate the help, the things you said jogged my brain into thinking the problem was further up the code :rolleyes:

Powered by vBulletin® Version 4.2.2 Copyright © 2015 vBulletin Solutions, Inc. All rights reserved.