taking average only if its not null

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

Help

Expression

Data Investigation

Accepted answers

JosephSerpis

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

Average_null_040919.yxmd

All comments

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-and-Nulls/td-p/319797

JosephSerpis

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

Average_null_040919.yxmd

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!

unknown

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.

Average-NULL.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280