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

- Home
- Acrobat SDK
- Discussions
- Re: Calculate Average Excluding Null Values

- Re: Calculate Average Excluding Null Values

Calculate Average Excluding Null Values

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/td-p/9873043
May 09, 2018
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

Community guidelines

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

18
Replies
18

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873044#M21974
May 09, 2018
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

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873045#M21975
May 09, 2018
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

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

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

Copy link to clipboard

Copied

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

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

Copy link to clipboard

Copied

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873048#M21978
May 16, 2018
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

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873049#M21979
May 16, 2018
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

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873050#M21980
May 16, 2018
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

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873051#M21981
May 16, 2018
May 16, 2018

Copy link to clipboard

Copied

You didn't answer my questions.

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873053#M21983
May 17, 2018
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

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

Copy link to clipboard

Copied

Can you share the actual file with us?

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873056#M21986
May 18, 2018
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

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

Copy link to clipboard

Copied

You need to exclude zero values as well as blanks.

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873058#M21988
May 18, 2018
May 18, 2018

Copy link to clipboard

Copied

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

Community guidelines

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

Copy link to clipboard

Copied

Don't count the zero values.

Community guidelines

LMPhill
AUTHOR

Community Beginner
,

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

Copy link to clipboard

Copied

Thank you!!

Community guidelines

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873052#M21982
May 16, 2018
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

LMPhill
AUTHOR

Community Beginner
,

/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873054#M21984
May 17, 2018
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

LEGEND
,

LATEST
/t5/acrobat-sdk-discussions/calculate-average-excluding-null-values/m-p/9873061#M21994
May 18, 2018
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

Copyright © 2023 Adobe. All rights reserved.