5 Replies Latest reply on Feb 14, 2017 2:17 PM by Karl Heinz Kremer

    translate excel code to javascript

    michaels66063507

      I'm trying to take a credit card payment calculator code from an excel spreadsheet and translate it into javascript. I'm way out of my league. I've reviewed other similar posts and attempted to mimic the information, but I keep getting syntax errors. Here is the code I'm trying to translate:

       

      =IFERROR(ROUNDUP(NPER('Payoff Calculator'!C13/12,-'Payoff Calculator'!C15,'Payoff Calculator'!C12,0),0),"N/A")

       

      Can anyone help? Thanks.

        • 1. Re: translate excel code to javascript
          try67 MVP & Adobe Community Professional

          Instead of sending us to dig in the documentation of the various Excel functions, can you explain what it is supposed to do, exactly?

          • 2. Re: translate excel code to javascript
            gkaiseril MVP & Adobe Community Professional

            Do you need the whole statement translated or do you just need the function for the number of payment periods?

            • 3. Re: translate excel code to javascript
              michaels66063507 Level 1

              =IFERROR(ROUNDUP(NPER('Payoff Calculator'!C13/12,-'Payoff Calculator'!C15,'Payoff Calculator'!C12,0),0),"N/A")

               

              Sorry, I forgot that the formula referenced cells in a separate worksheet. Here are those references:

               

              C13 = interest rate

              C15 = proposed monthly payment

              C12 = balance owed

               

              The formula is to calculate how long it would take to pay off a credit card balance given the interest rate and proposed monthly payment.

               

              I guess I need the whole statement translated. I'm hoping my acrobat form will function the same as the excel spreadsheet I have.

              As I go through this spreadsheet, I see that there is a second formula for calculating total interest that I would also need:

               

              =IFERROR(((NPER('Payoff Calculator'!C13/12,-'Payoff Calculator'!C14,'Payoff Calculator'!C12,0)*'Payoff Calculator'!C14)-'Payoff Calculator'!C12),"N/A")

               

              C14 = minimum monthly payment

               

              Thank you for any help you can provide.

              • 4. Re: translate excel code to javascript
                Test Screen Name Most Valuable Participant

                We're not Excel programmers. I can guess what IFERROR does. There's no real equivalent of IFERROR but you can test for an NaN result, or better, detect and prevent bad calculations (e.g. by testing for division by zero). What ROUNDUP does we can guess, but NPER? That's what we meant by  "Instead of sending us to dig in the documentation of the various Excel functions, can you explain what it is supposed to do, exactly?" What does it do, expressed in simple math rather than Excel code?

                • 5. Re: translate excel code to javascript
                  Karl Heinz Kremer Adobe Community Professional

                  Excel is a very powerful calculation tool and has a lot of functions built in, which you would need to implement from scratch in any other environment. The NPER function calculates the "Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate." (that's from the Microsoft documentation. There is nothing like that in JavaScript, which means that you would have to know how that function works, and what it does behind the scenes. Your best bet is to find code that somebody already created for that purpose. A quick Google search turned up this: JavaScript Functions

                   

                  The problem here is that this code was written for a browser based JavaScript environment, which does not work in Acrobat, you will need to remove the HTML parts so that only the JavaScript part remains. You can then call the function from your own calculation script. If you want to know how you would convert what you find on that web page to something that works in Acrobat, take a look here: Learning to Program JavaScript for Adobe Acrobat - KHKonsulting LLC  - that page also gives some pointers about how to learn to program in JavaScript for Acrobat.

                   

                  As far as the ROUNDUP function goes, you can do that using Math.ceil(): Math.ceil() - JavaScript | MDN