11 Replies Latest reply on May 27, 2016 11:51 PM by Test Screen Name

    How do I convert Excel formula to Javascript?

    Coleen77 Level 1

      I tried using Online Excel Formula Beautifier to convert an Excel formula to javascript for use in Adobe Acrobat Pro XI >Forms>Text Field Properties>Custom Calculation Script but it didn't work.  (I first changed the Excel cell numbers to the correct names of the (Acrobat) form fields in the following calculation)

       

      Excel formula using field names: 

      =IF(Goals=1,GA1,IF(Goals=2,(SUM(GA1,GB1)/2),IF(Goals=3,(SUM(GA1,GB1,GC1)/3),IF(Goals=4,(SU M(GA1,GB1,GC1,GD1)/4)))))

       

      Converted Javascript calculation using above website: 

      (Goals===1,GA1,IF(Goals==2,((GA1,GB1)/2),IF(Goals==3,((GA1,GB1,GC1)/3),IF(Goals==4,((GA1,G B1,GC1,GD1)/4)))))

       

      There is no error message, but no results in the field. I would love it if someone could help me with this script.  Thanks!!!


      Windows 8.1, Acrobat Pro XI

        • 1. Re: How do I convert Excel formula to Javascript?
          George_Johnson MVP & Adobe Community Professional

          Whatever that JavaScript is, it's not complete. Something a bit more readable would be:

           

          // Get the field values, as numbers

          var Goals = +getField("Goals").value;

          var GA1 = +getField("GA1").value;

          var GB1 = +getField("GB1").value;

          var GC1 = +getField("GC1").value;

          var GD1 = +getField("GD1").value;

           

          // Set this field value

          switch (Goals) {

          case 1:

              event.value = GA1;

              break;

          case 2:

              event.value = (GA1 + GB1) / 2;

              break;

          case 3:

              event.value = (GA1 + GB1 + GC1) / 3;

              break;

          case 4:

              event.value = (GA1 + GB1 + GC1 + GD1) / 4;

              break;

          default:

              event.value = "";

              break;

          }

           

           

          This assumes you have fields with those names.

          • 2. Re: How do I convert Excel formula to Javascript?
            Coleen77 Level 1

            Perfect.  Thank you ever so much!!  This has been so much help.

             

            Only one more formula conversion to Javascript - if you would be so kind...  Then I am done with my PDF Form. Thanks again.

             

                                                    =(C1E*0.4)+(GA1E*0.6)

            • 3. Re: How do I convert Excel formula to Javascript?
              George_Johnson MVP & Adobe Community Professional

              For that you don't need to use JavaScript and instead use the simplified field notation option, and enter:

               

              (C1E*0.4)+(GA1E*0.6)

               

               

              But if you want to use JavaScript, it could be:

               

               

              event.value = 0.4 * getField("C1E").value + 0.6 * getField("GA1E").value;

               

              So now you should know how to get the field values of the input fields and use some of the arithmetic operators (additional, multiplication, division).

              • 4. Re: How do I convert Excel formula to Javascript?
                Coleen77 Level 1

                Thank you again!!  Yes, I am learning from this, believe me.  I love learning this stuff.  

                Both calculations are working fine -

                 

                But now I'm getting a Warning:JavaScript Window error  "The value entered does not match the format of the field [GA1E]" 

                GA1E is the name of the field I put your first script in.  

                When I change the "Goals" value then try to change the GA1 or GB1 or GC1 or GD1 values, the error comes up.  I click OK and I'm carried down to that GA1E field and the same box pops up a second time.  I press OK and then repeat the process, still seeing the error messages, until I have all my numbers in.  Meanwhile, the calculations work properly.  It's just the annoying pop up error boxes.

                Now I deleted the "Goals" dropdown field and tried recreating it.  This annoying Warning comes up every time I add a field.  I'm still working on it.  Any suggestions?


                Thanks again.

                • 5. Re: How do I convert Excel formula to Javascript?
                  George_Johnson MVP & Adobe Community Professional

                  What format have you set up for the GA1E field? What does it show when you set it to None temporarily? That sort of error is common for a numeric field when you do a division by zero, but that script does not divide by zero.

                  • 6. Re: How do I convert Excel formula to Javascript?
                    Coleen77 Level 1

                    I'm good now that I changed the two fields with your calculation scripts in them to the option "None" in Format.  Now all the errors are gone and the calculations work fine.  Except -- the results in the calculation with the .4 and .6 (your second script) now has numerous zeroes after the decimal and a number a the end of the zeros.  I tried limiting the characters in the field, but that didn't work. Any suggestions?  We're almost there.....

                    • 7. Re: How do I convert Excel formula to Javascript?
                      George_Johnson MVP & Adobe Community Professional

                      OK, I think I know what happened. The result of the calculation was probably a number very close to zero and was represented in exponential notation (e.g. 1e-12 for 0.000000000001). The formatting routines that Acrobat uses don't deal with this result correctly, so it generates an error when this occurs. You can instead do the rounding yourself by using the following custom calculation script:

                       

                      event.value = util.printf("%.2f", 0.4 * getField("C1E").value + 0.6 * getField("GA1E").value);


                      This rounds to the nearest 100th and you can change the formatting back to numeric.

                      • 8. Re: How do I convert Excel formula to Javascript?
                        Coleen77 Level 1

                        Thank you again.  Later today when I get back to my desk, I will replace the (second) custom script you wrote for me with the third and change the formatting back to numeric.  You've been very helpful and I do appreciate your time. 

                        • 9. Re: How do I convert Excel formula to Javascript?
                          Coleen77 Level 1

                          I changed the script (util.printf) and now the decimals are showing correct on that field. Of course there's more... 

                          Now something similar started happening in the field with script #1 (switch):  2.3333333333334 or 6.6666666666666667 etc.(when the results come back in infinite decimals, that is.)

                          I also tried changing the format back to numeric on these cells, but started having the same error messages again so for now I'm keeping it at None and it works okay.

                           

                          Thanks,

                          Coleen

                          • 10. Re: How do I convert Excel formula to Javascript?
                            designbyfit

                            I am having a similar problem as the original poster.  I need to output a value from a formula that would be written in Excel as such:

                            =b-(IF(b<20.5,b,20.5))+5+36.8.

                             

                            It comes out

                             

                            =b - (    IF(

                                    b < 20.5,

                                    b,

                                    20.5

                                ) ) + 5 + 36.8

                            • 11. Re: How do I convert Excel formula to Javascript?
                              Test Screen Name Most Valuable Participant

                              Designbyfit, the online "converter" claims it can beautify or convert to JavaScript. But you chose the first option. The result however still wouldn't be much good; you need to learn both language, never heard of a working converter to Acrobat JavaScript.