Trouble with nested IF formulas

Katie_Hayes · ‎May 11, 2018

I am trying to create an event expense tracking table. In one column, I want to know if an event is on or over budget. I have a column, “Total”, where I will record the amount the event cost. Ideally I would then like the next column to be populated with whether it was on or over budget.

I am trying to write a formula where Total under 1500 populates the formula column with “On Budget”, Total over 1500 populates the formula column with “Over Budget”, and Total that is blank leaves the formula column blank. This is what I have written:

IF(Total<1500, “On Budget”, “Over Budget”), IF(Total=BLANK, BLANK)

But an error pops up that reads, “Sorry, there was a problem saving this field. Invalid formula. Please check your formula text.” What am I doing wrong?

Jeremy_Oglesby · ‎May 11, 2018

The idea with “nested” IF() formulas (aka, conditionals), is that they have to actually be “nested” - or in other words, one conditional needs to be inside of the other.

Here’s your formula put into proper nesting format:

IF(Total<1500, "On Budget", IF(Total>1500, "Over Budget", IF(Total=BLANK(),BLANK())))

Here’s the same formula spread out to make it easier to see the structure:

IF(
  Total<1500,
  "On Budget",
  IF(
    Total>1500,
    "Over Budget",
    IF(
      Total=BLANK(),
      BLANK()
    )
  )
)

(you can copy-paste either of those into Airtable; it accepts both)

The flow in natural language goes like this:
IF Total is less than 1500,
THEN show “On Budget”,
OTHERWISE,
IF Total is greater than 1500,
THEN show “Over Budget”,
OTHERWISE,
IF Total is blank,
THEN show blank.

Hopefully that helps you understand how nested conditionals work a little better.

Also - may I suggest adding a budget field for each event? What if you find yourself with an event that has a budget other than $1500? If you add a field where you declare the budget for that event, then you can replace the “1500” in your formula with a reference to the {Budget} field, and it will always calculate for the specific budget of that event.

EDIT:
Another suggestion - I like using emoji in fields like this that are serving as a warning of some sort to catch people’s attention. (Plus, it just adds some color, life, and fun to you base!).

IF(..."🔵 On Budget", ... "🔴 Over Budget"...)

It makes visual scanning a lot easier and more enjoyable.

Katie_Hayes · ‎May 14, 2018

This was extremely helpful. Thank you so much!

Katie_Hayes · ‎May 14, 2018

I followed your advice about creating a budget field. But when I type a number into the budget field and keep the total field blank, it is still populating the formula field with “Under Budget”. Why is that?

Jeremy_Oglesby · ‎May 14, 2018

Oops :grimacing: When a conditional is checking values, if it finds a match at any point in the conditional it stops there, processes the value, and ignores the rest of the conditional. It is treating a blank value in “Total” as $0, and as a blank - but the first one that registers in the conditional I wrote is $0, because it is less than $1500 - so the conditional never makes it to evaluating a blank field - it stops early, satisfied that it has found its match. We just need to make the
IF(Total=BLANK(), BLANK()…)
the first statement in the conditional instead of the last!
My bad :hugs:

W_Vann_Hall · ‎May 14, 2018

As I don’t see an IF() branch reading ‘Under budget,’ I’ll assume you mean ‘On budget.’ If that’s the case, you’ll probably want to rearrange your branchings, as BLANK() evaluates as 0 (zero) in some contexts:

IF(
    Total=BLANK(),
    BLANK(),
    IF Total<1500, 
        'On Budget', 
        IF(
            Total>1500, 
            'Over Budget'
            )
        )
    )

Jeremy_Oglesby · ‎May 14, 2018

Thank you @W_Vann_Hall for writing out the new format for the formula - much more helpful! I tried to answer on my phone, and am having a hell of a time with formatting on this little keyboard and interface.

Aparna_Kothary · ‎May 14, 2018

What if I wanted to have multiple IF statements. For example

If Cell 1 =>1 and Cell 2>=1 then return “stage 1”, if cell 1>=2 and cell 2>=2 then return “stage 2”

Jeremy_Oglesby · ‎May 14, 2018

You can include two other types of logical arguments within a conditional statement - AND() and OR().
EDIT: actually there are some others, but these are the main ones you will use 99% of the time

AND() requires that every statement inside it returns true - if they do, it also returns true; if not, it returns false.

OR() requires that at least one statement inside it returns true (it stops evaluating as soon as it finds one that is true) - if at least one statement returns true, it returns as true, if not, it returns as false.

IF(AND({Cell 1} >= 1, {Cell 2} >= 1), "stage 1", IF(AND({Cell 1} >= 2, {Cell 2} >= 2), "stage 2"))

Or spread out to show the structure:

IF(
  AND(
    {Cell 1} >= 1,
    {Cell 2} >= 1
  ),
  "stage 1",
  IF(
    AND(
      {Cell 1} >= 2,
      {Cell 2} >= 2
    ),
    "stage 2"
  )
)

The AND() statements in that formula will only evaluate as TRUE if both of the >= statements inside them evaluate as TRUE.

Katie_Hayes · ‎May 15, 2018

Thank you Jeremy_Oglesby and W_Vann_Hall! You are both very helpful :slightly_smiling_face: