6 Replies Latest reply on Sep 20, 2013 8:35 AM by try67

    Need help creating a custom formula

    calkinsdm

      I have a staff evaluation form for which I need to create a custom calculation script. We have a rating scale of 1,2,3,4,N/O (not observed). I need a formula that will not include any indicators that were marked "N/O" but will calculate an average of all the numbers chosen and place it on the form.

       

      formula.JPG

      The formula needs to look at all fields and determine if they are a numeric value or N/O, add all the fields with a numeric value together and then divide by the total number of ratings that have a numeric value. For example:

       

      1.01 = 4

      1.02 = 3

      1.03 = N/O

      1.04 = 3

      1.05 = 3

      1.06 = 4

      1.07 = N/O

       

      I need the formula to add 1.01,1.02,1.04,1.05, and 1.06 together and divide by 5 instead of dividing by 7 because all the indicators were not observed.

        • 1. Re: Need help creating a custom formula
          try67 MVP & Adobe Community Professional

          What are the names of the drop-down fields?

          • 2. Re: Need help creating a custom formula
            calkinsdm Level 1

            The field names are 1.01, 1.02, 1.03, etc.

            • 3. Re: Need help creating a custom formula
              gkaiseril MVP & Adobe Community Professional

              This is a fairly common question. It will require a custom JavaScript to perform the the task of adding and counting selected fields then then performing the special calculation.

               

              Have you searched for "average" or "averaging"?

               

              The following script will average an array of numeric values or optionally numeric values not equal to a given value.

               

              function AverageIFNE(aValues, cValue) {

              var fAverage = "";

              /*

              var nCount = CountIFNE(aValues, cValue);

              if (nCount != 0) {

              fAverage = SumIFNE(aValues, cValue) / nCount;

              }

              */

              var nCount = 0; // variable for sum of non-null items;

              var fSum = 0; // variable for count of non-null items;

              if(cValue == "undefined") {

              // process the array of field values with no restriction

              for(i = 0; i < aValues.length; i++) {

              if(!isNaN(aValues[i])) { // test for non-null value or other values;

              nCount++; // increment non-null count;

              fSum += Number(aValues[i]); // increment non-null count;

              } // end not a number processing

              } // end field name processing;

              } else {

              // process the array of field values with restriction

              for(i = 0; i < aValues.length; i++) {

              if(!isNaN(aValues[i]) && aValues[i] != cValue ) { // test for non-null value or other values;

              nCount++; // increment non-null count;

              fSum += Number(aValues[i]); // increment non-null count;

              } // end not number processing

              } // end field name processing

              } // end cValue test

              if (nCount != 0) {

              // compute average for none zero count result

              fAverage = SumIFNE(aValues, cValue) / nCount;

              }

              return fAverage;

              }

               

              You will need to create an array of values from your field to be processed and then just call the function with the array values to be processed and since the value you want to exclude is not a number you do not need ot specify the optional value.

               

              // array of field names to process

              var aFields = new Array ("Field1", "Field2", "Field3","Field4", "Field5","Field6", "Field7");

              // create an array of the values for the named fields

              var aValues = new Array();

              for (i = 0; i < aFields.length; i++) {

              aValues[i] = this.getField(aFields[i]).value;

              }

              // call average if ne function

              event.value = AverageIFNE(aValues);

              • 4. Re: Need help creating a custom formula
                try67 MVP & Adobe Community Professional

                Ah, OK. In that case you can use this code as the custom calculation script of your average text field:

                 

                var n = 0;

                var total = 0;

                for (var i=1; i<=7; i++) {

                    var f = this.getField("1.0"+i);

                    if (/^\d$/.test(f.valueAsString)) {

                        n++;

                        total += Number(f.valueAsString);

                    }

                }

                if (n==0) event.value = "";

                else event.value = total/n;

                • 5. Re: Need help creating a custom formula
                  calkinsdm Level 1

                  I also have indicators that have field names that are 2.01, 2.02, etc; 3.01, 3.02, etc; and 4.01, 4.02, etc. Do I just change the 1.0 to 2.0, 3.0, or 4.0 to have this formula calcuate those ranges as well?

                   

                  Thanks,

                  David

                  • 6. Re: Need help creating a custom formula
                    try67 MVP & Adobe Community Professional

                    Yes, you just need to change the base field name...

                    1 person found this helpful