Need Average function to ignore null values

I am attempting to find the average of given fields. The Average function is returning the average, but not how I need it. Some of the fields have null values and I need the function to ignore them. For example: If I'm finding the average of 7 fields and 1 is null, the sum should be divided by 6 instead of 7.

The null field could be any of the 7.

Preparation

Accepted answers

AdamR_AYX

OK well if all your values are 0-10 then how about this for a solution:

Use a multi-field formula to update all of your values with this expression:

iif(isnull([_CurrentField_]), 0, [_CurrentField_] + 1)

i.e. set nulls to zero and increase every other value by 1.

Then use the summarise tool with "average - ignore nulls" for all of your fields.
Then use a second multi-field formula to update all of the newly created averages with this expression:

[_CurrentField_] - 1

Which subtracts the one we added to all of the values in the first expression.

Adam

All comments

mbarone

For me personally, since the Summarize Tool has no "average - ignore nulls", I update the nulls to zero before the Summarize Tool (then you can use the "average - ignore zeros") using either the Formula Tool or the Multi-Field Formula Tool (depending on how many fields I'm updating).

For the formula itself, if you use "ToNumber([FieldName])" just like that, and the field is numeric, then it will fill the nulls with zeros. Or, depending on my need, I may filter out the non-nulls and then use the Summarize Tool with just those non-nulls.

justin_norris

That's a good suggestion, but I left out a key piece of info. I am working with a 0-10 points system. A zero means they scored very poorly (obviously) and should be included in the calculation. If the field is null, that means a score wasn't reported and should not be included in the calculation. Nulls must be carried through as nulls for reporting purposes.

I could write a ton of nested If statements, but there are up to 13 fields needed in the calculation. I would need an If statement for every possible combination...there has to be an easier way.

AdamR_AYX

OK well if all your values are 0-10 then how about this for a solution:

Use a multi-field formula to update all of your values with this expression:

iif(isnull([_CurrentField_]), 0, [_CurrentField_] + 1)

i.e. set nulls to zero and increase every other value by 1.

Then use the summarise tool with "average - ignore nulls" for all of your fields.
Then use a second multi-field formula to update all of the newly created averages with this expression:

[_CurrentField_] - 1

Which subtracts the one we added to all of the values in the first expression.

Adam

doug_helmreich

Apparently fall is the time to try to take averages inclusive of null values. AdamR's solution works for single fields, but if you want to take an average across multiple fields you need to first transpose the data, then summarize, then merge back in -- a pain. SPSS has a simple mean.x function that allows you to specify the maximum number of missing values that will allow it to return an average without error. Would love a similar functionality in Alteryx.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703