jquery - Converting Excel formula to JavaScript Calculation - Stack Overflow

admin管理员组

文章数量:1430037

I have this forumula in a Excel sheet :

=IF(B$3=0,B$14*B$2*B$6,(1-((1+B$3))^B$6)/LN(1/(1+B$3))*B12*B$2)

where cells:

B2 = 40000

B3 = 1.0%

B6 = 30

B12 = 10.0%

B14 = 20.0%

The excel produces the following result: 139,834

I started looking at the 'else' part seeing as B3 not equal to 1, however I having issues with my calculations in JavaScript. Below is what I got (code is inside a function):

    var B2 = 40000; 
    var B3 =  1/100; 
    var B6 = 30; 
    var B12 = 10/100;
    var B14 = 20/100;

    var calc = Math.pow ((1-(1+B3)),B6) / Math.log ( (1 / (1+B3)) * B12 * B2);

    return ( calc );

What I'm getting back is 1.2071318349401829e-61

Anyone know where I'm going wrong or how I would go about getting the correct result in JavaScript?

Any help appreciated.

I have this forumula in a Excel sheet :

=IF(B$3=0,B$14*B$2*B$6,(1-((1+B$3))^B$6)/LN(1/(1+B$3))*B12*B$2)

where cells:

B2 = 40000

B3 = 1.0%

B6 = 30

B12 = 10.0%

B14 = 20.0%

The excel produces the following result: 139,834

I started looking at the 'else' part seeing as B3 not equal to 1, however I having issues with my calculations in JavaScript. Below is what I got (code is inside a function):

    var B2 = 40000; 
    var B3 =  1/100; 
    var B6 = 30; 
    var B12 = 10/100;
    var B14 = 20/100;

    var calc = Math.pow ((1-(1+B3)),B6) / Math.log ( (1 / (1+B3)) * B12 * B2);

    return ( calc );

What I'm getting back is 1.2071318349401829e-61

Anyone know where I'm going wrong or how I would go about getting the correct result in JavaScript?

Any help appreciated.

• javascript
• jquery
• excel
• math

Share Improve this question Follow edited Jul 22, 2011 at 14:29 Sukh asked Jul 22, 2011 at 14:20 SukhSukh 2522 silver badges55 bronze badges 2

• in your js, B14 is worth 10% (VS 20% in Excel), is it a typo ? – JMax Commented Jul 22, 2011 at 14:22
• Yes sorry that is a type it should be 20% - var B14 = 20/100; – Sukh Commented Jul 22, 2011 at 14:28

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

In excel you have

1-((1+B$3))^B$6

which is the same as

1-(1+B$3)^B$6

in javascript you have

Math.pow ((1-(1+B3)),B6)

which is different

本文标签： jqueryConverting Excel formula to JavaScript CalculationStack Overflow