Skip to main content
Solved

Help with nested if/and formula

  • November 14, 2024
  • 4 replies
  • 36 views
AndreaR
Forum|alt.badge.img+9

Hi there -  I am attempting to track contract breach status (pending, resolved, or n/a) depending on the source of the breach and whether there is a specific response date. 

My formula will be 5 if statements, of which 4 are also if/and. I've been able to add the first two successfully but when I try to add the third, nothing happens. Any help would be most appreciated! Thank you in advance! 

  1. If source of breach = avoidable loss, “n/a- avoidable loss”
  2. If source of breach = notice of agreement & noa received date >1, resolved otherwise pending
  3. If source of breach = repayment overdue & resolution date >1, resolved otherwise pending
  4. If source of breach = ownership transfers & resolution date >1, resolved otherwise pending
  5. If source of breach = miscellaneous >1, resolved otherwise pending
IF({Source of Breach}="Avoidable Loss","n/a",IF(AND({Source of Breach}="Notice of Agreement",{NOA Received Date}>"1"),"Resolved","Pending",IF(AND({Source of Breach}="Repayment Overdue",{Resolution Date}>"1","Resolved","Pending"))))

Best answer by Alexey_Gusev

Hi,
I suppose ">1" means "not empty"
Also, condition 5 is not quite clear. I suppose it means if {Source of Breach} is anything else (not empty) then resolved.
I wonder why did you set {Source of Breach} to be multiple select? Do you understand that if you put, for example both "Notice of Agreement" and "Avoidable Loss" in value,. 
IF({Source of Breach}="Avoidable Loss"  will not work and IF({Source of Breach}="Notice of Agreement" will not work too. 
If you expect {Source of Breach} to have only one value you can do it Single-select. And if it can have multiple values, to check whether value exists or not, you should check it as 
IF(FIND(Value,{Source of Breach}) ,  true..  , false)

To check whether date exists or the cell is empty, do a simple IF({Field}, .....

About your formula, it written in a wrong way. Typical IF should be
IF( condition , output_when_true , output_when_false )  - third parameter could be omitted

for several IFs it could be nested
IF( condition , output_when_true , 
  IF( condition2 , output_when_true , 
    IF( condition3 , output_when_true , output_when_false )  ) ) 

You are wrote

IF( condition , output_when_true , output_when_false,
  IF (condition2, .....) 

that's not correct

anyway, I would use switch for you case.
two last lines means ' If SOB empty, then 'Pending' , if anything else then Resolved '

SWITCH({Source of Breach}, 'Avoidable Loss','N/A', 'Notice of Agreement',IF({NOA Received Date},'Resolved','Pending'), 'Repayment Overdue',IF({Resolution Date},'Resolved','Pending'), 'Ownership Transfers',IF({Resolution Date},'Resolved','Pending'), '','Pending', 'Resolved')




4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

Hm, in your conditions, you mention "ffif received date >1" and it's equivalent in the formula appears to be '{NOA Received Date}>"1"'.  What does >1 mean in this context?  That a `ffif received date` exists? 

Adam, Airtable Consultant - thetimesaving.company
AndreaR
Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • Answer
  • November 15, 2024

Hi,
I suppose ">1" means "not empty"
Also, condition 5 is not quite clear. I suppose it means if {Source of Breach} is anything else (not empty) then resolved.
I wonder why did you set {Source of Breach} to be multiple select? Do you understand that if you put, for example both "Notice of Agreement" and "Avoidable Loss" in value,. 
IF({Source of Breach}="Avoidable Loss"  will not work and IF({Source of Breach}="Notice of Agreement" will not work too. 
If you expect {Source of Breach} to have only one value you can do it Single-select. And if it can have multiple values, to check whether value exists or not, you should check it as 
IF(FIND(Value,{Source of Breach}) ,  true..  , false)

To check whether date exists or the cell is empty, do a simple IF({Field}, .....

About your formula, it written in a wrong way. Typical IF should be
IF( condition , output_when_true , output_when_false )  - third parameter could be omitted

for several IFs it could be nested
IF( condition , output_when_true , 
  IF( condition2 , output_when_true , 
    IF( condition3 , output_when_true , output_when_false )  ) ) 

You are wrote

IF( condition , output_when_true , output_when_false,
  IF (condition2, .....) 

that's not correct

anyway, I would use switch for you case.
two last lines means ' If SOB empty, then 'Pending' , if anything else then Resolved '

SWITCH({Source of Breach}, 'Avoidable Loss','N/A', 'Notice of Agreement',IF({NOA Received Date},'Resolved','Pending'), 'Repayment Overdue',IF({Resolution Date},'Resolved','Pending'), 'Ownership Transfers',IF({Resolution Date},'Resolved','Pending'), '','Pending', 'Resolved')




AndreaR
AndreaR
Forum|alt.badge.img+9
  • AndreaR
  • Author
  • Inspiring
  • November 15, 2024

Hm, in your conditions, you mention "ffif received date >1" and it's equivalent in the formula appears to be '{NOA Received Date}>"1"'.  What does >1 mean in this context?  That a `ffif received date` exists? 


Correct! If there is a date then the breach is "resolved." Also, my mistake, "ffif received date" was "noa received date" so I edited.

AndreaR
Forum|alt.badge.img+9
  • AndreaR
  • Author
  • Inspiring
  • November 15, 2024

Hi,
I suppose ">1" means "not empty"
Also, condition 5 is not quite clear. I suppose it means if {Source of Breach} is anything else (not empty) then resolved.
I wonder why did you set {Source of Breach} to be multiple select? Do you understand that if you put, for example both "Notice of Agreement" and "Avoidable Loss" in value,. 
IF({Source of Breach}="Avoidable Loss"  will not work and IF({Source of Breach}="Notice of Agreement" will not work too. 
If you expect {Source of Breach} to have only one value you can do it Single-select. And if it can have multiple values, to check whether value exists or not, you should check it as 
IF(FIND(Value,{Source of Breach}) ,  true..  , false)

To check whether date exists or the cell is empty, do a simple IF({Field}, .....

About your formula, it written in a wrong way. Typical IF should be
IF( condition , output_when_true , output_when_false )  - third parameter could be omitted

for several IFs it could be nested
IF( condition , output_when_true , 
  IF( condition2 , output_when_true , 
    IF( condition3 , output_when_true , output_when_false )  ) ) 

You are wrote

IF( condition , output_when_true , output_when_false,
  IF (condition2, .....) 

that's not correct

anyway, I would use switch for you case.
two last lines means ' If SOB empty, then 'Pending' , if anything else then Resolved '

SWITCH({Source of Breach}, 'Avoidable Loss','N/A', 'Notice of Agreement',IF({NOA Received Date},'Resolved','Pending'), 'Repayment Overdue',IF({Resolution Date},'Resolved','Pending'), 'Ownership Transfers',IF({Resolution Date},'Resolved','Pending'), '','Pending', 'Resolved')





Thank you, Alexey! Appreciate the time you took to assist!

First, the column should have been single select, good catch! I was playing around with a multiple select option since in some cases one contract may have multiple breaches, but decided against this.

Your switch formula worked and I am definitely going to read more about that since I think it will help in some other places as well. 

Thank you, thank you!!!

Alexey_Gusev
Terms & ConditionsAccessibility statement