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

Question

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!

Best answer by Mike_AutomaticN

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

Mike_AutomaticN · Accepted Answer

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

Kenneth_Raghuna · Answer

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 NationSome weird behavior that I noticed when checking this one out:If a number field value is 0...=BLANK() returns true!= BLANK() also returns trueMeaning 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})

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded