Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

/t5/acrobat-discussions/calculation-of-the-average-of-fields/td-p/8812802
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:

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

Community guidelines

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

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

...
24
Replies
24

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812803#M36318
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.

Community guidelines

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

Mary Knuth
AUTHOR

New Here
,

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812804#M36319
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"]);

Community guidelines

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

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812805#M36320
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".

Community guidelines

Mary Knuth
AUTHOR

New Here
,

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812806#M36321
Jan 27, 2017
Jan 27, 2017

Copy link to clipboard

Copied

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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812807#M36322
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!

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812808#M36323
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.

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812809#M36324
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.

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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812810#M36325
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.

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812811#M36326
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.

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812812#M36327
Sep 30, 2018
Sep 30, 2018

Copy link to clipboard

Copied

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812813#M36328
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.

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812814#M36329
Oct 04, 2018
Oct 04, 2018

Copy link to clipboard

Copied

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812815#M36330
Oct 05, 2018
Oct 05, 2018

Copy link to clipboard

Copied

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812816#M36331
Oct 06, 2018
Oct 06, 2018

Copy link to clipboard

Copied

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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812817#M36332
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.

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812818#M36333
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"]);

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812819#M36334
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?

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812820#M36335
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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812821#M36336
Oct 06, 2018
Oct 06, 2018

Copy link to clipboard

Copied

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812822#M36337
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;

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812823#M36338
Oct 06, 2018
Oct 06, 2018

Copy link to clipboard

Copied

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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812824#M36339
Oct 06, 2018
Oct 06, 2018

Copy link to clipboard

Copied

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

Community guidelines

/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812825#M36340
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!

Community guidelines

LEGEND
,

LATEST
/t5/acrobat-discussions/calculation-of-the-average-of-fields/m-p/8812826#M36341
Oct 06, 2018
Oct 06, 2018

Copy link to clipboard

Copied

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

Community guidelines

Resources

About Adobe Acrobat

Adobe Inc

Adobe Inc

Adobe Inc

Adobe Acrobat Feature & Workflow

FAQs

Using the Community
Experience League
Terms of Use
Privacy Policy
Cookie preferences
AdChoices
Language:

- Deutsch
- English
- EspaĆ±ol
- FranĆ§ais
- ę„ę¬čŖć³ćć„ććć£
- PortuguĆŖs
- ķźµģ“

Copyright Ā© 2022 Adobe. All rights reserved.