Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

How to fix formula to only calculate if input value is 0 or not empty?

Topic Labels: Formulas
Solved
Jump to Solution
142 3
cancel
Showing results for 
Search instead for 
Did you mean: 
dnn6
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
Mike_AutomaticN
10 - Mercury
10 - Mercury

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

See Solution in Thread

3 Replies 3
Mike_AutomaticN
10 - Mercury
10 - Mercury

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})

 

Alexey_Gusev
13 - Mars
13 - Mars

Hi,
Use

 

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

 

 

Alexey_Gusev_0-1740050302958.png

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

Alexey_Gusev_1-1740050965010.png