Calculate Average Excluding Null Values

[LMPhill]

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.

[gkaiseril]

You need to write a custom JavaScript calculation that excludes the months that have a null value for the average expenses for the month. This assumes your monthly average for a future month has a null values if that month has not been past or the details for the month have not been entered. So this calculation may start with the computation of the monthly average.

[LMPhill]

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.

[try67]

I posted a function that does exactly that. Search the forums for "average calculation" or something similar and you should find it.

[LMPhill]

Thank you, but I'm still having the same issue. I'm sure it's something fairly simple. I just don't know what it is!

[LMPhill]

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.

[try67]

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

[LMPhill]

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.

[try67]

You didn't answer my questions.

[LMPhill]

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!

[try67]

Can you share the actual file with us?

[LMPhill]

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.

[try67]

You need to exclude zero values as well as blanks.

[LMPhill]

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

[Bernd Alheit]

Don't count the zero values.

[LMPhill]

Thank you!!

[gkaiseril]

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.

[LMPhill]

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

Thanks for your help!

[gkaiseril]

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.