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

- Home
- >
- Acrobat SDK
- >
- Calculate Average Excluding Null Values

Calculate Average Excluding Null Values

Community Beginner
,

May 09, 2018

Copy link to clipboard

Copied

Thanks in advance for your help!

I have a form that tracks expenses for a six month period and tracks an average for each expense listed. Three months in, you get the average for January, February and March; April, May and June would not be included in the average since there are no values in those fields.

The calculations work out pretty well until I try averaging the TOTAL EXPENSES during that same period; for example, the AVERAGE TOTAL January expenses, February expenses and March. It results in a average of all six months, not just the three with data entered.

Here is the script for the AVERAGE TOTAL Monthly Expenses:

event.value = Avg('ESExpenseMonthly_01_TOT', 'ESExpenseMonthly_02_TOT', 'ESExpenseMonthly_03_TOT', 'ESExpenseMonthly_04_TOT', 'ESExpenseMonthly_05_TOT', 'ESExpenseMonthly_06_TOT');

Again, thanks for your help.

TOPICS

Acrobat SDK and JavaScript,
Macintosh

Community Guidelines

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

Calculate Average Excluding Null Values

Community Beginner
,

May 09, 2018

Copy link to clipboard

Copied

Thanks in advance for your help!

I have a form that tracks expenses for a six month period and tracks an average for each expense listed. Three months in, you get the average for January, February and March; April, May and June would not be included in the average since there are no values in those fields.

The calculations work out pretty well until I try averaging the TOTAL EXPENSES during that same period; for example, the AVERAGE TOTAL January expenses, February expenses and March. It results in a average of all six months, not just the three with data entered.

Here is the script for the AVERAGE TOTAL Monthly Expenses:

event.value = Avg('ESExpenseMonthly_01_TOT', 'ESExpenseMonthly_02_TOT', 'ESExpenseMonthly_03_TOT', 'ESExpenseMonthly_04_TOT', 'ESExpenseMonthly_05_TOT', 'ESExpenseMonthly_06_TOT');

Again, thanks for your help.

TOPICS

Acrobat SDK and JavaScript,
Macintosh

Community Guidelines

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

LMPhill

May 09, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/td-p/9873043
18
Replies
18

Adobe Community Professional
,

May 09, 2018

Copy link to clipboard

Copied

Community Guidelines

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

Reply

Loading...

gkaiseril

May 09, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873044#M21974
Community Beginner
,

May 09, 2018

Copy link to clipboard

Copied

Thank you very much!

There is also some document script associated with this document that seems to take care of the averages of the monthly expenses. It does not seem to work with the calculated fields (January Total, February Total, etc.) Using the script above, the result is an average of all six months, even though three are empty.

Thanks again for your help.

Community Guidelines

Reply

Loading...

LMPhill

May 09, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873045#M21975
Most Valuable Participant
,

May 09, 2018

Copy link to clipboard

Copied

Community Guidelines

Reply

Loading...

try67

May 09, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873046#M21976
Community Beginner
,

May 10, 2018

Copy link to clipboard

Copied

Community Guidelines

Reply

Loading...

LMPhill

May 10, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873047#M21977
Community Beginner
,

May 16, 2018

Copy link to clipboard

Copied

Thanks in advance for your help!

I'm still can't seem to get this script to calculate a rolling average of the monthly totals when the total fields are calculated. This is what I have so far:

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(["ESExpenseMonthly_01_TOT", "ESExpenseMonthly_02_TOT", "ESExpenseMonthly_03_TOT", "ESExpenseMonthly_04_TOT", "ESExpenseMonthly_05_TOT", "ESExpenseMonthly_06_TOT"]);

Any help would be greatly appreciated.

Community Guidelines

Reply

Loading...

LMPhill

May 16, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873048#M21978
Most Valuable Participant
,

May 16, 2018

Copy link to clipboard

Copied

What happens when you use it? Are there any error messages in the JS Console?

Community Guidelines

Reply

Loading...

try67

May 16, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873049#M21979
Community Beginner
,

May 16, 2018

Copy link to clipboard

Copied

Thanks for your help!

The code listed above is in the ESExpenseMonthly_TOT_AVG field. It is supposed to be an average of ESExpenseMonthly_01_TOT, ESExpenseMonthly_02_TOT, ESExpenseMonthly_03_TOT, ESExpenseMonthly_04_TOT, ESExpenseMonthly_05_TOT, and ESExpenseMonthly_06_TOT. These are calculated fields (total of all first month's expenses, total of all second month's expenses, etc.) and represent a six-month spread. As long as all six months are completed, the calculation is correct. The problem is that it's May; I started this in March (only three months ago). The calculation still averages all six months (not just March, April, and May), so the total average is off.

Any assistance you could offer would be greatly appreciated.

Community Guidelines

Reply

Loading...

LMPhill

May 16, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873050#M21980
Most Valuable Participant
,

May 16, 2018

Copy link to clipboard

Copied

You didn't answer my questions.

Community Guidelines

Reply

Loading...

try67

May 16, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873051#M21981
Adobe Community Professional
,

May 16, 2018

Copy link to clipboard

Copied

Assuming your monthly calculations set the field for the specific month´s average to a null string if no data for that month is present, you can use the ¨String¨ constrictor to force the field´s value to ¨¨ and you can use that to test if a given field has a null value.

Another approach is to place the ¨valueAsString¨ for each field into an array, use the array filter method to remove any null values and then compute the average of the fields remaining in the array.

Community Guidelines

Reply

Loading...

gkaiseril

May 16, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873052#M21982
Community Beginner
,

May 17, 2018

Copy link to clipboard

Copied

Thanks, try! I guess I didn't state it clear enough. Using the code described in my note, the TOTAL FIELD displays an average, but for all six months. This is a problem especially when someone is just starting the worksheet. There is no error message on the IS console. Does that help?

Thanks!

Community Guidelines

Reply

Loading...

LMPhill

May 17, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873053#M21983
Community Beginner
,

May 17, 2018

Copy link to clipboard

Copied

Thanks, gk! I'm not very familiar those functions. Can you point me toward some good resources?

Thanks for your help!

Community Guidelines

Reply

Loading...

LMPhill

May 17, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873054#M21984
Most Valuable Participant
,

May 17, 2018

Copy link to clipboard

Copied

Can you share the actual file with us?

Community Guidelines

Reply

Loading...

try67

May 17, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873055#M21985
Community Beginner
,

May 18, 2018

Copy link to clipboard

Copied

Thanks again for your help! In the example above, I expected the TOTAL AVERAGE to be $1.00; it currently displays $0.17. If I were to fill in the rest of that row, the answer would be correct; but I am looking for a rolling average (as I am completing this worksheet for six months.

It seems like it should be fairly simple, but I'm just not getting it. Thanks for your help.

Community Guidelines

Reply

Loading...

LMPhill

May 18, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873056#M21986
Most Valuable Participant
,

May 18, 2018

Copy link to clipboard

Copied

You need to exclude zero values as well as blanks.

Community Guidelines

Reply

Loading...

try67

May 18, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873057#M21987
Community Beginner
,

May 18, 2018

Copy link to clipboard

Copied

That's the ticket!!!! Works perfectly now! Thank you SOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO much!

Community Guidelines

Reply

Loading...

LMPhill

May 18, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873058#M21988
Adobe Community Professional
,

May 18, 2018

Copy link to clipboard

Copied

Don't count the zero values.

Community Guidelines

Reply

Loading...

Bernd Alheit

May 18, 2018
1
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873059#M21989
Community Beginner
,

May 18, 2018

Copy link to clipboard

Copied

Thank you!!

Community Guidelines

Reply

Loading...

LMPhill

May 18, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873060#M21992
gkaiseril
LATEST

Adobe Community Professional
,

May 18, 2018

Copy link to clipboard

Copied

I use the following user defined document level function to compute the average of an array of numerical values only. The function returns a null value if the average cannot be computed because there are no values to average.

function Average(data){

/*

purpose: compute neaan average of values in an array or null if no values in array;

input: data = array of values to compute;

return computed average of null if not computed;

*/

var avg = null;

var sum = data.reduce(function(sum, value){

return sum + Number(value);

}, 0);

if(data.length > 0) {

avg = sum / data.length;

}

return avg;

} // end average function;

I move all the values no matter the source of the value being from a field, variable or a constant value. Once the array of values has been populated I use the filter method of the array object to remove any unwanted values like the "" string or null. I can then compute the average of the items left in the array.

// filter out empty string values or null values from array;

function NotNull(element) {

return element != "" && element != null;

}

// aply NotNull filter to array of values;

aValues = aValues.filter(NotNull);

// compute average of values in the array;

event.value = Average(aValues);

Sample Average Function form

Note that zero values and null values can be valid values for computing an average. Examples are Acrobat's "Field is the _____ of the following fields:", MS Excel's Average function. In more generalized applications measuring temperatures will need to include zero and negative values. When writing a function is a good practice to include as many possible options for the computation a possible and then use custom code to eliminate the unwanted values, since one might have to compute several averages but have different exclusions on the allowed values.

Community Guidelines

Reply

Loading...

gkaiseril

May 18, 2018
0
/t5/acrobat-sdk/calculate-average-excluding-null-values/m-p/9873061#M21994
Using the Community
Experience League
Terms of Use
Privacy Policy
Cookie preferences
AdChoices
Language:

- Deutsch
- English
- Español
- Français
- 日本語コミュニティ
- Português

Copyright © 2020 Adobe. All rights reserved.