Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- IF Formula Causing Trouble with Blank and Zero Amo...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1514
8

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2024 01:39 PM - edited Feb 21, 2024 04:59 PM

I'm trying to build an IF Formula that seems like it would be straight-forwarded, but is proving to be more complicated than I thought it would be and has got me stumped. Both fields are Lookup fields getting a Rollup value from another table.

I started with a basic IF formula, but the problem is it resulted in X $0 values being replaced by the Y value, when I wanted the $0 X value to be returned.

IF(

X,

X,

Y)

Then I tried to a formula to say if X is equal to or greater than 0, and that worked, but it would not return Y if the X Column was blank.

IF(

X >=0,

X,

Y)

So, I tried an IF / Or Formula. It still returned the X field correctly, but did not return the Y Field if the X field was blank. (Update: I originally wrote this wrong when I posted. I was trying to say if X is not blank & is equal to or more than 0, return X, otherwise return Y. Also tried with X !="" instead of Blank() - same result).

IF(

OR(

X >= 0,

X != Blank()),

X,

Y)

I'm not sure why this is being so complicated and I can't get the Y value to return if the X value is blank.

Help! I'm running out of ideas. Thank you!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2024 11:37 PM

Ah, try this:

```
IF(
OR(
{B (from Data)},
{B (from Data)} & "" = '0'
),
{B (from Data)},
{A (from Data)}
)
```

Reply

8 Replies 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2024 02:24 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2024 03:00 PM

I tried that too... same results... 😞

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2024 04:33 PM

Could you provide a base with some example data to help with troubleshooting?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2024 04:39 PM

It's very simple.

Don't use BLANK().

You can use IF(X, 'value if X not blank' ). You can add 3rd parameter, it will be returned if X is blank.

IF(X, - is the check for 'non-emptiness'

IF(X>=0 - check for 'not negative'

It's two different IFs that should be combined

Unfortunalely, I cannot understand you goal.

if X>=0 then show X

If X is blank show Y

what if X<0 ??

Also, I might be wrong, but using new lines should divide different condition operators, for better readability. The way you use it, new line after each word in the same operator, making readability even worse than without new lines at all.

Indeed, I'm not a saint in the questions of code/formula formatting and conventions, so please consider it as my humble opinion.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2024 05:03 PM

Thank you for offering to look at this. Here is a sample base I created.

The problem exists on "Expenses Combined" table in the 💠 Expense ❖ Field, Record #2.

I want the formula to return the $0 amount from the "Y-Exp SubLevel Total ",

not the $95 amount from the "X-Exp Top Level Total".

Basically, I am building a Profit and Loss page in Airtable and X is the Top Level Expenses and Y is the Sub-Level Expenses.

If the total of the Y-Sublevel is $0, I want the 💠 Expense ❖ Field to show $0. If the Y-Sublevel is Blank, I want the 💠 Expense ❖ Field to show the "X-Exp Top Level Total" amount.

The Formula used for 💠 Expense ❖ is:

IF(

{Y - SubLevel Exp Total (from Y - SubLevel)}="",

{Total Top Level Exp (from X - Main Exp)},

IF({Y - SubLevel Exp Total (from Y - SubLevel)} >=0,

{Y - SubLevel Exp Total (from Y - SubLevel)},

{Total Top Level Exp (from X - Main Exp)}

))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2024 05:07 PM

I appreciate the constructive feedback regarding my formula writing... I by no means am correct in my style of formula writing - but it works for me. 😊

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 23, 2024 11:37 PM

Ah, try this:

```
IF(
OR(
{B (from Data)},
{B (from Data)} & "" = '0'
),
{B (from Data)},
{A (from Data)}
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 24, 2024 05:51 PM

Thank you so much, Adam @TheTimeSavingCo, for looking at that and figuring out a solution - and another helpful formula building idea for me that I can use in the future!