Skip to main content

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.



Thanks in advance!

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

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}

   )

)

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.

Reply


Terms & Conditions