Skip to main content
L
LMPhill
Known Participant
May 9, 2018
Question

Calculate Average Excluding Null Values

  • May 9, 2018
  • 3 replies
  • 3181 views

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.

This topic has been closed for replies.

3 replies

L
LMPhillAuthor
Known Participant
May 16, 2018

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
Community Expert
try67Community Expert
Community Expert
May 16, 2018

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

L
LMPhillAuthor
Known Participant
May 16, 2018

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
Community Expert
try67Community Expert
Community Expert
May 9, 2018

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

L
LMPhillAuthor
Known Participant
May 10, 2018

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!

G
gkaiseril
Inspiring
May 9, 2018

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.

L
LMPhillAuthor
Known Participant
May 9, 2018

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices