Forum Discussion

BBIUser's avatar
BBIUser
Helper IV
7 years ago
Solved

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 -

 

Appreciate your help. Thanks!