Excel formulas to Javascript Math

Question

I have a number of formulas which are in Excel form and I know I have asked for help on these kind of things before but I have three more formulas that I cannot work out how to express in javascript.

the variables are

var J17 = 6.50;
var C19 = 5;
var C20 = 6;
var C22 = 0.5;
var E26 = 0.5; 
var F26 = 0.5;

Formula 1:

=1/(1+J$17*C$22*0.01)^((E26/C$22)) - Should return 0.968523002

Formula 2:

=IF(F26=C$19,100,0) - Should return 0.00

Formula 3:

=IF(E26>C$19,0,C$20*C$22) - Should return 3.00

Thats is my main problem is with Formula 1, I know that the ^ is expressed using Math.pow but I cannot work out where it should go in the javascript expression. I have had a try at this:

var calcValue = (1 /  Math.pow(1 + J17 * C22 * 0.01), (E26/C22) );

But it returns infinity

Your IF statements can be replaced with the format someCondition? valueiftrue : valueiffalse. So for the first one: F26 === C19? 100 : 0 — Matt Burland
– Matt Burland, Commented Dec 4, 2014 at 22:11
@MattBurland what would be the statement for this =IF(E26>C$19,0,E26) — Justin Erswell
– Justin Erswell, Commented Dec 4, 2014 at 22:33
Well, E26>C19 would be your condition, 0 would be your valueiftrue and E26 would be your value if false. So just plug them in. — Matt Burland
– Matt Burland, Commented Dec 5, 2014 at 2:11

Madbreaks · Accepted Answer · 2014-12-04 22:06:36Z

2

You've got a paren out of place:

var calcValue = (1 /  Math.pow(1 + J17 * C22 * 0.01), (E26/C22)
                ^

Try:

var calcValue = 1 /  Math.pow((1 + J17 * C22 * 0.01), (E26/C22))
                             ^

For what it's worth, your original formula has a useless extra set of parens too: ((E26/C$22))

EDIT Updated + 0.01 to * 0.01, as your original post had that same error. ;)

answered Dec 4, 2014 at 22:06

Madbreaks

19.6k7 gold badges62 silver badges75 bronze badges

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

3 Comments

Justin Erswell Over a year ago

Thanks that is producing a result (jsfiddle.net/9pgrw20f) but not the expected

Matt Burland Over a year ago

Doesn't give the right answer unless you replace the +0.01 with *0.01

Matt Burland Over a year ago

Also, you only actually need 1 / Math.pow(1 + J17 * C22 * 0.01, E26/C22 ); Those inner brackets aren't doing anything.

Collectives™ on Stack Overflow

Excel formulas to Javascript Math

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related