taking average only if its not null

[BosKev]

Hi All,

I want to take an average of 3 fields only if it has value in it, if it shows Null then average only the available numbers ( 2 of 3 has value then I only want the average of those 2)

is there an easier way to do this other than a if statement?

Thanks,

BosKev

Example:

Global Fee Group	Type1	Type2	Type3	Ideal Output
C	Null	750	Null	750
B	Null	Null	Null	0
C	3756	2500	Null	3128
B	3400	5400	6615	5138

[agrawaluk]

Hi @BosKev ,

I think the following post might be helpful for you. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Average-calculation-excluding-zeros-an...

[JosephSerpis · Accepted solution]

Hi @BosKev I mocked up a workflow let me know what you think? 

[BosKev]

Thank you both, but @JosephSerpis solution has fewer steps.

[Igor_Garlowski]

In this case the author didn't have any zeros so this solution works; what if we have zeros and we want to not count only nulls?

[john007]

Than you so much for this.   I had a similar issue and was able to solve it with minor tweaks to your workflow.  Much appreciated.   Happy New Year!

[Bilbottom]

This is not the 'Alteryx way' to do this, but you can also do this with the formula tool -- but it'll get messy and long which several variables.

For this approach, define the following columns:

numerator (Int64)

0
+ ToNumber([Type1])
+ ToNumber([Type2])
+ ToNumber([Type3])

denominator (Int64)

-1 * (0
    + !IsNull([Type1])
    + !IsNull([Type2])
    + !IsNull([Type3])
)

average (Int64)

IF [denominator] = 0

    THEN 0

    ELSE [numerator]/[denominator]
ENDIF

You can also call the columns anything you want, but the average column above gives you what you want.

Note that ToNumber will implicitly replace Nulls with 0, IsNull returns -1 when True (hence the multiplication by -1), and defining average as Int64 will round to the nearest integer.