Help

Second "IF" in nested statement returning no result

Topic Labels: Formulas
1170 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.