Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Calculation error, different result from excel

1. ## Calculation error, different result from excel

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

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

• 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

• 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.

• Originally Posted by Kor
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?

• Originally Posted by Furton
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.

• Got it working, thanks all

Think I might be using this forum more often now

• 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.

• Originally Posted by Furton
Got it working, thanks all

Think I might be using this forum more often now
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?

• 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

•

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•