- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 19, 2025 11:19 AM
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!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 19, 2025 11:29 AM
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}
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 19, 2025 11:29 AM
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}
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 19, 2025 02:02 PM
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})
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 20, 2025 03:18 AM - edited ‎Feb 20, 2025 03:30 AM
Hi,
Use
IF({Monthly Actual} != BLANK(),{Monthly Actual} - {Monthly Goal})
That's really unexpected, so using "!=" for that case is more reliable than using "="
