Excel formulas to javascript

Question

Im trying to use an excel formula into my javascript code but cannot get the correct result, here is the formula in excel:

(C4-(C8/C6))*(1-((1+(C5/12))^-C6))/(C5/12)+C8+C7

and how i try it in javascript:

(737-(50000/300))*(1-(Math.pow((1+(0.02/12)),-300)))/(0.02/12)+50000+10000

or

(737-(50000/300))*(1-((1+(0.02/12))**-300))/(0.02/12)+50000+10000

Both attempt give me same result but this result is not the same as in excel, any idea?

Javascript give me 194558.80507389922 but excel give me 191516,666

are you sure the excel formula is working as you wish?

Argee
– Argee

2020-09-21 09:08:08 +00:00
Commented Sep 21, 2020 at 9:08 — Argee
– Argee, Commented Sep 21, 2020 at 9:08

Peter B · Accepted Answer · 2020-09-21 09:21:15Z

1

When I try it myself in Excel, the formula produces 194559,8051 (same as JS).

When I change C5 from 0,02 to 0,022 then the formula produces 191516,666 (same as your Excel).

Conclusion -> your C5 is actually 0,022, or something very close to it. Please make sure your cells are set to show as many decimals as needed to avoid display-only rounding.

edited Sep 21, 2020 at 9:21

answered Sep 21, 2020 at 9:14

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christophe Over a year ago

Thanks a lot, you are right, switched the cells format to 3 decimals and it was 0.022.. thanks you for the help!

mplungjan · Accepted Answer · 2020-09-21 09:11:50Z

0

Same result to the 4 decimals my Excel rounded with default and same result to 7 decimals

NOTE: Is floating point math broken?

const C4 = 737;
const C5 = 0.02;
const C6 = 300;
const C7 = 10000;
const C8 = 50000;

// (C4-(C8/C6))*(1-((1+(C5/12))^-C6))/(C5/12)+C8+C7         = 194558,8051 in my Office 365 Excel
                                                           // 194558,805073901 - with 9 decimals
console.log(
(C4-(C8/C6))*(1-(Math.pow((1+(C5/12)),-C6)))/(C5/12)+C8+C7 // 194558.80507389922
)

console.log(
(C4-(C8/C6))*(1-((1+(C5/12))**-C6))/(C5/12)+C8+C7          // 194558.80507389922
)

edited Sep 21, 2020 at 9:11

answered Sep 21, 2020 at 8:49

mplungjan

180k29 gold badges183 silver badges246 bronze badges

2 Comments

Christophe Over a year ago

Thanks for the answer, im indeed getting 194558.80507389922 but what bother me is that excel give me 191516,666 ... adding the screen in initial message

mplungjan Over a year ago

I got 194558,805073901 in my Excel Office 365 with the values I displayed and your formula

Collectives™ on Stack Overflow

Excel formulas to javascript

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related