0 Replies Latest reply on May 9, 2017 10:40 AM by dianejmayne

    Convert Excel Formula to JavaScript

    dianejmayne

      Hello!

      I'm just starting to learn JavaScript; unfortunately I am attempting to build 3 calculations that are a bit out of my skill level at the moment. If anyone would be willing to help I would really appreciate it.

       

      Below is the first formula from excel. The intent of this formula is to add all values between 0 & 8 using data from 16 fields..."TOTAL#" are the accurate field names.

      =SUM(IF(TOTAL1<=8,TOTAL1,IF(TOTAL1>8,8))+IF(TOTAL2<=8,TOTAL2,IF(TOTAL2>8,8))+IF(TOTAL3<=8, TOTAL3,IF(TOTAL3>8,8))+IF(TOTAL4<=8,TOTAL4,IF(TOTAL4>8,8)),+IF(TOTAL5<=8,TOTAL5,IF(TOTAL5> 8,8))+IF(TOTAL6<=8,TOTAL6,IF(TOTAL6>8,8))+IF(TOTAL7<=8,TOTAL7,IF(TOTAL7>8,8))+IF(TOTAL8<=8 ,TOTAL8,IF(TOTAL8>8,8))+IF(TOTAL9<=8,TOTAL9,IF(TOTAL9>8,8))+IF(TOTAL10<=8,TOTAL10,IF(TOTAL 10>8,8))+IF(TOTAL11<=8,TOTAL11,IF(TOTAL11>8,8))+IF(TOTAL12<=8,TOTAL12,IF(TOTAL12>8,8))+IF( TOTAL13<=8,TOTAL13,IF(TOTAL13>8,8))+IF(TOTAL14<=8,TOTAL14,IF(TOTAL14>8,8))+IF(TOTAL15<=8,T OTAL15,IF(TOTAL15>8,8))+IF(TOTAL16<=8,TOTAL16,IF(TOTAL16>8,8)))

       

      Below is the second formula from excel. The intent of this formula is to add all values between 8 & 12 using data from 16 fields..."Total#" are the accurate field names.

       

      =SUM((IF(TOTAL1>=12,4,IF(TOTAL1>=8,TOTAL1-8,0)))+(IF(TOTAL2>=12,4,IF(TOTAL2>=8,TOTAL2-8,0) ))+(IF(TOTAL3>=12,4,IF(TOTAL3>=8,TOTAL3-8,0)))+(IF(TOTAL4>=12,4,IF(TOTAL4>=8,TOTAL4-8,0))) +(IF(TOTAL5>=12,4,IF(TOTAL5>=8,TOTAL5-8,0)))+(IF(TOTAL6>=12,4,IF(TOTAL6>=8,TOTAL6-8,0)))+( IF(TOTAL7>=12,4,IF(TOTAL7>=8,TOTAL7-8,0)))+(IF(TOTAL8>=12,4,IF(TOTAL8>=8,TOTAL8-8,0)))+(IF (TOTAL9>=12,4,IF(TOTAL9>=8,TOTAL9-8,0)))+(IF(TOTAL10>=12,4,IF(TOTAL10>=8,TOTAL10-8,0)))+(I F(TOTAL11>=12,4,IF(TOTAL11>=8,TOTAL11-8,0)))+(IF(TOTAL12>=12,4,IF(TOTAL12>=8,TOTAL12-8,0)) )+(IF(TOTAL13>=12,4,IF(TOTAL13>=8,TOTAL13-8,0)))+(IF(TOTAL14>=12,4,IF(TOTAL14>=8,TOTAL14-8 ,0)))+(IF(TOTAL15>=12,4,IF(TOTAL15>=8,TOTAL15-8,0)))+(IF(TOTAL16>=12,4,IF(TOTAL16>=8,TOTAL 16-8,0))))

       

       

      Below is the third formula from excel. The intent of this formula is to add all values over 12 using data from 16 fields..."Total#" are the accurate field names.

      =SUM((IF(TOTAL1>12,TOTAL1-12,0))+(IF(TOTAL2>12,TOTAL2-12,0))+(IF(TOTAL3>12,TOTAL3-12,0))+( IF(TOTAL4>12,TOTAL4-12,0))+(IF(TOTAL5>12,TOTAL5-12,0))+(IF(TOTAL6>12,TOTAL6-12,0))+(IF(TOT AL7>12,TOTAL7-12,0))+(IF(TOTAL8>12,TOTAL8-12,0))+(IF(TOTAL9>12,TOTAL9-12,0))+(IF(TOTAL10>1 2,TOTAL10-12,0)))+(IF(TOTAL11>12,TOTAL11-12,0))+(IF(TOTAL12>12,TOTAL12-12,0))+(IF(TOTAL13> 12,TOTAL13-12,0))+(IF(TOTAL14>12,TOTAL14-12,0))+(IF(TOTAL15>12,TOTAL15-12,0))+(IF(TOTAL16> 12,TOTAL16-12,0))

       

      I know they are long but hopefully there is an element of copy/paste and just changing the field names to it.

       

      Appreciate the help/advice anyone has.

       

      Thanks!