Skip to main content

I'm having a really hard time with making a formula field only calculate a value if the input field is not empty, including if it's 0. For some reason Airtable seems to treat 0 and empty value that same.

Here is my current formula: 

IF(
{Monthly Actual} = BLANK(),
BLANK(),
{Monthly Actual} - {Monthly Goal}
)

In the attached screenshot, I want the fields with monthly actual = 0 to have a difference calculated, and I want the empty monthly actual to not have a difference calculated. The latter works, but the former doesn't. I've tried ChatGPT, Perplexity...etc. all types of AI and can't figure it out!

Hey @dnn6!

I might be missing what it is that you are trying to achieve, but give the following a shot:

IF(
AND({Monthly Actual} = BLANK(),{Monthly Actual}!=0),
BLANK(),
{Monthly Actual} - {Monthly Goal}
)


Mike, Consultant @ Automatic Nation

Hey @dnn6!

I might be missing what it is that you are trying to achieve, but give the following a shot:

IF(
AND({Monthly Actual} = BLANK(),{Monthly Actual}!=0),
BLANK(),
{Monthly Actual} - {Monthly Goal}
)


Mike, Consultant @ Automatic Nation


Some weird behavior that I noticed when checking this one out:

If a number field value is 0...

=BLANK() returns true

!= BLANK() also returns true

Meaning that this would also currently work:

IF({Monthly Actual} != BLANK(),
{Monthly Actual} - {Monthly Goal})

That said, I've emailed AT to clarify whether the function is supposed to return true in both of these cases, or if this is a bug.

Airtable's official method (on the support article for the BLANK() function) recommends converting to a string before checking against BLANK() when seeking to define a number field as blank even if it has a 0, like so:

IF(BLANK()=CONCATENATE(Number,""), "Yes")

Here's what it would look like for your function:

IF({CONCATENATE(Monthly Actual},"") != BLANK(),
{Monthly Actual} - {Monthly Goal})

 

Hi,
Use

 

IF({Monthly Actual} != BLANK(),{Monthly Actual} - {Monthly Goal})

 

 

That's really unexpected, so using "!=" for that case is more reliable than using "=" 

 

Reply


Terms & Conditions