cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BBIUser Member
Member

How to handle NULL in a Custom Column?

Hi,

 

I have this one Custom Column "Notable Change?" value as 'Error'. I need to display it as 'None' instead. 

How do I handle null in the below formula? Sample screenshot is below and Error is highlighted in Red.

 

Here is the Custom Column formula:

=if [cCount]>0 and [ThreeYrAvg]>0 and 
([cCount] - [ThreeYrAvg]>=5.0) and 
((([cCount] * 1.00 - [ThreeYrAvg]) / [ThreeYrAvg]) * 100) >= 10.00
then "Increase"

else if [cCount]>0 and [ThreeYrAvg]>0 and 
([cCount] - [ThreeYrAvg]<=-5.0) and
((([cCount] * 1.00 - [ThreeYrAvg]) / [ThreeYrAvg]) * 100) <= -10.00
then "Decrease"

else if ([cCount]>0 and [ThreeYrAvg] = null) 
then "none"

else "None"

Here is how the sample data looks like -

2018-10-24_1103.png

 

Appreciate your help. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to handle NULL in a Custom Column?

@BBIUser,

 

Just move the condition below to the first.

[ThreeYrAvg] = null
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: How to handle NULL in a Custom Column?

@BBIUser,

 

Just move the condition below to the first.

[ThreeYrAvg] = null
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BBIUser Member
Member

Re: How to handle NULL in a Custom Column?

Thanks @v-chuncz-msft. That works neat!