We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## taking average only if its not null

8 - Asteroid

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

6 REPLIES 6
8 - Asteroid
17 - Castor

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

8 - Asteroid

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

7 - Meteor

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?

5 - Atom

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!

7 - Meteor

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.

Labels