Calculation of the Average of Fields

New Here ,
Jan 27, 2017 Jan 27, 2017

Copy link to clipboard

Copied

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:

Screen Shot 2017-01-27 at 10.23.40 AM.png

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?

TOPICS
PDF forms

Views

3.6K

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jan 27, 2017 Jan 27, 2017

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 valu

...

Likes

Translate

Translate
LEGEND ,
Jan 27, 2017 Jan 27, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 27, 2017 Jan 27, 2017

Copy link to clipboard

Copied

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"]);

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 27, 2017 Jan 27, 2017

Copy link to clipboard

Copied

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".

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Jan 27, 2017 Jan 27, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 29, 2018 Sep 29, 2018

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 29, 2018 Sep 29, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 29, 2018 Sep 29, 2018

Copy link to clipboard

Copied

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.

Screen Shot 2018-09-29 at 6.06.29 PM.png

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"]);

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Sep 30, 2018 Sep 30, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 30, 2018 Sep 30, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 30, 2018 Sep 30, 2018

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 01, 2018 Oct 01, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 04, 2018 Oct 04, 2018

Copy link to clipboard

Copied

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. 

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 05, 2018 Oct 05, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

RTI - Google Drive

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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"]);

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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

version?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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;

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 06, 2018 Oct 06, 2018

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines