Calculation of the Average of Fields

[Mary Knuth]

I am creating a form that has several fields (9) for a "Rating" (NA, 1,2,3,4) and want the average of the fields.

I have:

Problem is if the field is blank or NA is chosen, than it is calculated as a ZERO and throws off the average result.

Any ideas?

[gkaiseril · Accepted solution]

That is a pretty common approach used when software assumes how a calculation should be done. Excel excludes non-numeric data from both the sum and count but  throws an error condition when all the input fields are empty. The only way around this is to create a custom calculation script to compute the average and exclude the "N/A" items from the sum and the count of items to be averaged.

I would put the values of the fields as strings into an array, filter out any null entries or non-numeric values form the array and then process the resulting array of values when the length of the array is not zero.

Once could also create a for loop to read each field and only accept numeric values for the count and sun and then compute the average when the count of non-null fields is not zero.

[Mary Knuth]

Thanks!! Tried using your logic and did come up with the answer:

function myAverageFunction(aNames) {

    // n = number of fields that have a numerical value

    // sum = some of values for the named fields

    var n = 0;

    var sum = 0;

    var v;

    // loop through array of name strings

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

      v = this.getField(aNames).valueAsString;

      // check for not a null string and not a space  and not is Not a Number

      if (v != "" & v != " " & isNaN(v) == false) {

         n++; // increment count

         sum += Number(v); // add to sum

      }

    }

    return (n != 0) ? (sum / n): "";

}

event.value = myAverageFunction(["Rating1", "Rating2", "Rating3", "Rating4", "Rating5"]);

[gkaiseril]

You can use the Number constrictor and isNaN nested to simplify the script:

function myAverageFunction(aNames) {
    // n = number of fields that have a numerical value
    // sum = some of values for the named fields
    var n = 0;
    var sum = 0;
    var v;
    // loop through array of name strings
    for (i = 0; i < aNames.length; i++) {
      v = this.getField(aNames).valueAsString;
      // check for not a null string and not a space  and not is Not a Number
      if (isNaN(Number(v)) == false) {
         n++; // increment count
         sum += Number(v); // add to sum
      }
    }
    return (n != 0) ? (sum / n): "";
}

event.value = myAverageFunction(["Question1", "Question2", "Question3", "Question4", "Question5", "Question6", "Question7", "Question8","Question9"]);

If you use check boxes then unselected items will have a value of "Off". One would then only need to test for values not equal to "Off".

[Mary Knuth]

Thanks for that tip! I deleted the extra scripting and works smoothly.

[nicole1923]

Hi!

This is my first time on Acrobat Pro DC and I am struggling!

I am trying to show in two parts of my document averages of some tables. In the first part, I used the code you wrote above and it worked (Thank you for your help on that!). My second table I would like to average has data points on page 1 and page 2. How can I get the average without causing the ones that have a blank to throw off my average? I have 36 data points.

Page one I have 5 data points and page 2 I have 31, but on page 1 is where I would like to show the average.

Another thing I noticed is that it won't auto update like the one at the beginning of my document. Maybe that's because the data points on page 2 is throwing it off?

Hope you can help!

Thank you!

[try67]

Use the code above but change this line:

if (isNaN(Number(v)) == false) {

To:

if (v!="" && isNaN(Number(v)) == false) {

If the results seem to be "delayed" then you probably need to check the fields calculation order and make sure that the average field is calculated after all the fields its dependent on.

[nicole1923]

I tried it. It doesn't seem to work. Does it make a difference that I'm on a Mac?

Here is a screenshot of my document and what I would like to do with it.

Here is what I have typed in the "custom calculation script":

function myAverageFunction(aNames) {

    // n = number of fields that have a numerical value

    // sum = some of values for the named fields

    var n = 0;

    var sum = 0;

    var v;

    // loop through array of name strings

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

      v = this.getField(aNames).valueAsString;

      // check for not a null string and not a space  and not is Not a Number

      if (v!="" && isNaN(Number(v)) == false) {

         n++; // increment count

         sum += Number(v); // add to sum

      }

    }

    return (n != 0) ? (sum / n): "";

}

event.value = myAverageFunction(["CALPGO1", "CALPGO2", "CALPGO3", "CALPGO4", "CALPGO5", "CALPGO6", "CALPGO7", "CALPGO8", "CALPGO9", "CALPGO10", "CALPGO11", "CALPGO12", "CALPGO12", "CALPGO13", "CALPGO14", "CALPGO15", "CALPGO16", "CALPGO17", "CALPGO18", "CALPGO19", "CALPGO20", "CALPGO21", "CALPGO22", "CALPGO23", "CALPGO24", "CALPGO25", "CALPGO26", "CALPGO27", "CALPGO528", "CALPGO29", "CALPGO30", "CALPGO31", "CALPGO32", "CALPGO33", "CALPGO34", "CALPGO35", "CALPGO40"]);

[try67]

The problem is with the calculation you used for the fields that you want to average.

You'll notice that they show "NaN". That stands for "Not a Number". It's happening because when the denominator field is empty you're in effect diving by zero, which is not an allowed operation. You need to change the way you're calculating those values and check if the field is empty. If so, the field should be empty.

[gkaiseril]

For the first input fields used in the calculations, I would set the fields to the  "Number" and this would eliminate the entry of non-numeric values. You will still need to prevent division by zero.

It might be helpful to provide a link to a shared copy of your form or a sample form to see if there are other issues with the form. JavaScript processing for a form can end when an error in any script  for a field occurs.

[nicole1923]

Ok. In a different section above it shows the same thing, but it still averages the fields I want it to average even with the "NAN". I think my problem is that the fields I want to average are on different pages. When I remove all of the fields that are on the second page, then the field where I want the average to go works. What do I type in the custom calculation script to indicate that "CALPGO6" to "CALPGO40" are on page 2?

[try67]

The script above should handle NaN values just fine. I just think it's not good practice to have such values shown, so I recommend you change the way your calculations work, to avoid them.

The page on which the fields are located is irrelevant.

[nicole1923]

Ok, I see. Only problem is I am new to this program and have no idea how to fix it. All of my scripts have been from other forums on this site where other users wanted similar results that I am trying to achieve. 

[try67]

If you shared a copy of your form we could help you sort it out... It's probably just the result of division by zero.

[nicole1923]

RTI - Google Drive

Above is the link to my form. Thank you for all of your help!

[try67]

Yes, the issue is division by zero, as I suspected.

To fix it you would need to use a custom calculation script.

For example, instead of this formula:

(O1#1/O1#2)*100

Use this script:

var v1 = Number(this.getField("O1#1").valueAsString);

var v2 = Number(this.getField("O1#2").valueAsString);

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

else event.value = (v1/v2)*100;

I would also recommend not using the hashtag symbol in your field names, as it is often reserved to marking different "widgets" of the same field group.

[nicole1923]

Great! Thank you!  I made all the changes and my form is looking great!

The only thing not working now is the average calculation named "baseline 2". It needs to calculate the average of any data that is entered in CALPGO1- CALPGO40. "Baseline" works perfectly above and I used the same custom calculation script and it doesn't calculate the same. This is the script that I have for "baseline 2":

function myAverageFunction(aNames) {

    // n = number of fields that have a numerical value

    // sum = some of values for the named fields

    var n = 0;

    var sum = 0;

    var v;

    // loop through array of name strings

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

      v = this.getField(aNames).valueAsString;

      // check for not a null string and not a space  and not is Not a Number

      if (v!="" && isNaN(Number(v)) == false) {

         n++; // increment count

         sum += Number(v); // add to sum

      }

    }

    return (n != 0) ? (sum / n): "";

}

event.value = myAverageFunction(["CALPGO1", "CALPGO2", "CALPGO3", "CALPGO4", "CALPGO5", "CALPGO6", "CALPGO7", "CALPGO8", "CALPGO9", "CALPGO10", "CALPGO11", "CALPGO12", "CALPGO12", "CALPGO13", "CALPGO14", "CALPGO15", "CALPGO16", "CALPGO17", "CALPGO18", "CALPGO19", "CALPGO20", "CALPGO21", "CALPGO22", "CALPGO23", "CALPGO24", "CALPGO25", "CALPGO26", "CALPGO27", "CALPGO528", "CALPGO29", "CALPGO30", "CALPGO31", "CALPGO32", "CALPGO33", "CALPGO34", "CALPGO35", "CALPGO40"]);

[try67]

Are there any error messages in the JS Console? Can you share the latest

version?

[nicole1923]

Yes, it says:

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

TypeError: this.getField(...) is null

9:AcroForm:Baseline 2:Calculate

Here is the latest version with the edits: RTI - Google Drive

[gkaiseril]

The fields you are trying to access have an error in their name or do not exist. When one access a field object, Acrobat will return a value of none for the field if it cannot be opened.

[try67]

To be able to pin-point the problematic name I would replace this line:

v = this.getField(aNames).valueAsString;

With this:

var f = this.getField(aNames);

if (f==null) {

     console.println("Error: " + aNames);

     break;

}

v = f.valueAsString;

[try67]

You misspelled this field name in the array: "CALPGO528"

[try67]

Also, there's no field called "CALPGO34"...

[nicole1923]

THANK YOU!! This as well as the mistakes you found in my form fixed it!! My form works perfectly!

Thank you so much for your time and expertise!

[gkaiseril]

You also are accessing a field more than once in your average calculation for the field BaseLine 2.