Skip to main content
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!

Try using X = “” instead of X = BLANK(). I’ve run into similar issues with the BLANK() function and using empty quotes fixed it for me.

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

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

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


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.

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


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

https://airtable.com/appE9CTlhS7bPq3ZS/shrHxtLRfpIIO5A3T/tblaslMAQgTXZOQgT/viwNVqBj9lsyTHR6C/fld1vozmUYqxKgjDG

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

 

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.


Thank you, I tried the Double IF formula before posting also, and still didn't receive the result I was looking for. I tried creating a Sample Base that I've linked above using the Double IF formula and no Blank() - still no luck. 

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. 😊

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

https://airtable.com/appE9CTlhS7bPq3ZS/shrHxtLRfpIIO5A3T/tblaslMAQgTXZOQgT/viwNVqBj9lsyTHR6C/fld1vozmUYqxKgjDG

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

 


Ah, try this:

 

IF(

OR(

{B (from Data)},

{B (from Data)} & "" = '0'

),

{B (from Data)},

{A (from Data)}

)

 

Ah, try this:

 

IF(

OR(

{B (from Data)},

{B (from Data)} & "" = '0'

),

{B (from Data)},

{A (from Data)}

)

 


Ding! Ding! Ding! We have a winner! That's it!
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! 

Reply


Terms & Conditions