1 Reply Latest reply on Jul 17, 2013 8:07 AM by gkaiseril

    Excell Formulas to Javascript

    csb102155 Level 1

      I just got a request to convert an Excel file to PDF, and make the form calculate.  The calculations are pretty complex (at least to me they are).  There are four different types of formulas needed.  I've copied the information below.  Can someone help me with writing the scripts for PDF that would accomplish what the Excel formulas do?  I don't even know if it's possible to do and where to start.

       

      Thanks

      Connie Bretes

       

      Break Date:
      =IF(A20>0,$A$3+D20," ")
      If there is a tag number entered for the corresponding row, the equation will take the date cast and add the number of days until it's break, and give the date of the break. If no tag number (or any form of ID)
      is entered into the tag number cell, a blank cell will be shown.

       

      Correction Factor (C.F.):
      =IF(N20>0,LOOKUP('Correction Factor'!A3,'Correction Factor'!$C$3:$C$104,'Correction Factor'!$D$3:$D$104)," ")
      If there is a number entered in the corresponding Total Load cell, the equation will look up the correction factor value from the list in the correction factor tab based on the length and diameter ratio calculated in column A in the Correction Factor Tab, and give the proper correction factor.  If no total load value is given, a blank cell will be shown.

       

      P.S.I.
      =IF(N20>0,ROUND(((N20/(3.1415926535*((L20/2)^2)))*M20),-1)," ")
      If there is a number entered in the corresponding Total Load cell, the equation will solve (total load) / (pi * (diameter / 2)^2) to get PSI, and then multiply by the corresponding Correction factor to give it the appropriate PSI value.  The round function then rounds it to the nearest 10 value.  If no total load value is given, a blank cell will be shown.

       

      Average:
      =IF(N20>0,ROUND(AVERAGE(O20:O21),-1)," ")
      If there is a number entered in the corresponding Total Load cell, the equation will average the values of the 2 breaks for the cylinders cast for that day and round to the nearest 10 value. If no load value is given, a blank cell will be shown.
        • 1. Re: Excell Formulas to Javascript
          gkaiseril MVP & Adobe Community Professional

          First there is no direct conversion from Excel formulas to Acrobat's JavaScript. You need to know that even the simplified field notation and the field selection for some calculations both use JavaScript behind the scenes to perform the calculation.

           

          There is no easy date calculation like in Excel in JavaScript, you need to write custom JavaScript to convert the date string to a date object and then convert the date object to a date unit value and perform your calculation and then convert the result back to the date object for conversion to a string value. The format of the date strings is very important for these conversions.

           

          You also need to be aware that JavaScript's use of the IEEE floating point standard for decimal numbers introduces an error when using JavaScript's Math.round method so you need to create rounding function. JavaScript's Math.round method only works for integers.

           

          You might want to start with the tutorials at the http://acrobatusers.com/tutorials

          or www.pdfscripting.com