Help

Nested IF Statement

Topic Labels: Formulas
Solved
Jump to Solution
1582 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Stacy_Goodchild
4 - Data Explorer
4 - Data Explorer

Hello,
I’m new to Airtable & I have a formula that an Airtable support doc says should work & I can get to work in Excel, but keeps returning an error in my Airtable base.

What I want to accomplish is this:
IF the Jan 22 Commission Field is not blank, Jan 22 Prof Calc should equal that.
Otherwise, IF Jan 22 Actual is blank, Jan Prof Calc should equal zero. IF it’s not blank, Jan 22 Prof Calc should equal Jan 22 Remaining.

Screen Shot 2022-01-25 at 11.17.10 AM

Thanks in advance!

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

A couple things:

  • No Airtable formula starts with “=”. Remove it.
  • <>"" should be != and that is asking “does not equal a blank string”, and you’re comparing number fields which won’t produce strings in the first place. A better way to check if a field is not blank, string or otherwise, is IF({field name}). If you want to check if it is blank or false you could also do NOT({field name})
  • If you ever want you outcome to be a proper number, never put any possible outcome from the nested IFs in quotes. "0" is a string, 0 is a number.

So the proper formula would be:

IF(
   {Jan 22 Commission}, 
   {Jan 22 Commission}, 
   IF(
      NOT({Jan 22 Actual}), 
      0, 
      {Jan 22 Remaining}
   )
)

See Solution in Thread

3 Replies 3
JonathanB
8 - Airtable Astronomer
8 - Airtable Astronomer

You need to remove the “=” from before the IF (Airtable syntax isn’t quite the same as Excel) and use != for “does not equal/is not”. So your formula will be:

IF({Jan 22 Commission}!="" {Jan 22 Commission}, IF({Jan 22 Actual}="", "0", {Jan 22 Remaining}))

Kamille_Parks
16 - Uranus
16 - Uranus

A couple things:

  • No Airtable formula starts with “=”. Remove it.
  • <>"" should be != and that is asking “does not equal a blank string”, and you’re comparing number fields which won’t produce strings in the first place. A better way to check if a field is not blank, string or otherwise, is IF({field name}). If you want to check if it is blank or false you could also do NOT({field name})
  • If you ever want you outcome to be a proper number, never put any possible outcome from the nested IFs in quotes. "0" is a string, 0 is a number.

So the proper formula would be:

IF(
   {Jan 22 Commission}, 
   {Jan 22 Commission}, 
   IF(
      NOT({Jan 22 Actual}), 
      0, 
      {Jan 22 Remaining}
   )
)

Thanks so much! I appreciate the formula & the explanation.