Excel formula with % in Javascript

Question

I'm trying to get the following Excel sum working in Javascript.

=H21*(1+H22)*(1+H23)

H21 is a base price like 6.4 H22 and H23 are a negative percentage like '-15%'.

H22 and H23 come from range sliders so I have them like '-15'.

For now I have:

let price = H21*(1+H22)*(1+H23);

Adding % signs obviously don't work. Why does this work in Excel and not in Javascript and how can I re-factor to do this in Javascript. Any lead on documentation would also be very nice!

Why does this work in Excel and not in Javascript That's like saying, why do I have to change gear in a manual car, and I don't have to in an automatic. Percent, means per 100, or IOW: divide by 100. H22/100 — Keith
– Keith, Commented Mar 27, 2018 at 14:14
In Javascript, % is used for Modulo - so, the Javascript 5%2 is the same as the Excel =MOD(5,2), while in Excel % means "Divide by 100 and change General formatting to Percentage formatting" - so Excel 2*15% is Javascript's 2*15/100 — Chronocidal
– Chronocidal, Commented Mar 27, 2018 at 14:22

Adam Hopkinson · Accepted Answer · 2018-03-27 14:13:30Z

1

A percentage can be expressed as a float from 0-1, so I think you could just divide your percentage figures (H22 + H23) by 100:

let price = H21*(1+(H22/100))*(1+(H23/100));

Using numbers with % symbols in JS won't work, because the sum will cast them as floats by dropping anything that isn't valid:

parseInt('15%') // 15

Excel is seeing the % and doing something a bit more clever (which I didn't previously know about).

answered Mar 27, 2018 at 14:13

Adam Hopkinson

28.8k9 gold badges74 silver badges105 bronze badges

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

4 Comments

Chronocidal Over a year ago

Excel is slightly clever, but not completely clever - =10+15% will output 1015% rather than 11.5. But, while Excel doesn't know what to do with a permille symbol (‰) or permyriad/basis point (‱), you can chain % symbols: =5% is 0.05 and =5%% is 0.0005 which is the same as basis point (frequently used in finance e.g. for loans/bonds)

Charles Williams Over a year ago

@Chronocidal - Did not know that %% worked- thanks. But why should 10+15%=11.5 rather than 10.15 =1015%?

Chronocidal Over a year ago

@CharlesWilliams If you use a decent calculator, then "10+15%" is treated as "10*1.15" - i.e. "10 + (10*15%)". What would you say 15% more than 10 was?

Arnold Oosterom Over a year ago

@AdamHopkinson Thanks! Was way to focused on the Excel formula. Feeling a bit simple now.

Collectives™ on Stack Overflow

Excel formula with % in Javascript

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related