Formula Help: Custom Formula Statement

[dshaw]

I need some formula suggestions or help.  Below are two formulas in which I am trying to assign a 1 if below a certain threshold.  I am trying to say if the metric is below a certain level or if it is blank then assign it a 1, else 0.  I have tried the following below but it is not picking up on the null or blank portion.  I will admit that I have a basic level of craftsmanship when it comes to writing formulas, so I would appreciate your help with the following below: 

If [Net_Patient_Rev_3Y_Growth]<0 or "" then 1 else 0 endif

If [Ebitda_3Y_Growth]<=-.10 or "" then 1 else 0 endif

[MarqueeCrew · Accepted solution]

@dshaw,

If we assume that the incoming data for these fields is a NUMERIC field, then "" isn't reasonable.  A number can be NULL but not blank.  IsEmpty() will test for either Null or Empty variables.

I happen to like In-Line IF statements:

IIF([Net_Patient_Rev_Growth] < 0 or IsEmpty([Net_Patient_Rev_Growth]),1,0)

IIF([Ebitda_3Y_Growth] < -.1 or IsEmpty([Ebitda_3Y_Growth]),1,0)

Cheers,

Mark

[brindhan · Accepted solution]

Hi @dshaw

You need a separate statement to deal with Nulls.

This should give you your result.

If [Net_Patient_Rev_3Y_Growth]<0  or

isnull( [Net_Patient_Rev_3Y_Growth]) then 1 else 0 endif

Cheers,

B

[dshaw]

Thank you!  Much appreciated. I learn something new everyday.