Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Second "IF" in nested statement returning no result

Topic Labels: Formulas
1874 2
cancel
Showing results for 
Search instead for 
Did you mean: 
PC1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello, my first post to this community. This formula was entered and accepted with no errors. The issue is the second IF (in this case {Tax Type}='Sales Tax) does not produce a result.

Here is the formula:
IF({Tax Type}=‘Bank Rec’,IF({Qtr}=1,‘1/31’,IF({Tax Type}=‘Sales Tax’,IF({Qtr}=2,‘3/20’))))

My statement is:
Statement 1: if the tax type is ‘Bank Rec’ and the Qtr=1 then the date 1/31 should display.
Statement 2: if the tax type is ‘Sales Tax’ and the Qtr=2 then the date 3/20 should display.

Here is what I have tried:

  1. If I change the tax types for e.g. put sales tax first and bank rec second then bank rec will be blank
  2. I have hand typed the formula in the field, I did not copy and paste
  3. I have tried changing the single apostrophe’s to double
    For some reason the second IF does not produce a result even though the formula does NOT error out.I have uploaded the image of the result. Thank you in advance. I hope i posted this correctly.

Ifstatement

2 Replies 2

Welcome to the community, @PC1! :grinning_face_with_big_eyes:

Thanks for sharing this logic breakdown. Long story short, the way you’ve nested your IF functions is partly to blame, but there’s also a flaw in the design that doesn’t match the logic you outlined.

Whenever you want more than one comparison to impact a given outcome—i.e. the tax type is “Bank Rec” AND Qtr = 1—the collection of comparisons need to be wrapped inside an AND function in the first part of the IF function.

Also, because the {Qtr} field is a multiple select (I think it should probably be a single select if you only want one option to be chosen), you need to compare that field value against a string, not a number.

With those two things in mind, Statement 1 on its own would look like this:

IF(AND({Tax Type}='Bank Rec', {Qtr}='1'), '1/31')

Statement 2 on its own would look like this:

IF(AND({Tax Type}='Sales Tax', {Qtr}='2'), '3/20')

Combining the two into a single function, you have this:

IF(AND({Tax Type}='Bank Rec', {Qtr}='1'), '1/31', IF(AND({Tax Type}='Sales Tax', {Qtr}='2'), '3/20'))
PC1
5 - Automation Enthusiast
5 - Automation Enthusiast

Justin, it worked. I really appreciate not only the reply but the quick and concise explanation. The AND function allows for the comparison i was looking for. Also I am glad you mentioned about the single select field. i have been using the multiple select field when i should have used the single select. all around a great learning experience. Don’t want to prolong this but i stumbled across AirTable and it is the best kept secret on the net. Thank you again Justin. Hope to be here again, sooner than later.