Forum Discussion
Nested IF's based on various criteria
Hey folks -brain is not working this morning.
I have various columns in which I am trying to create a calculation of time buckets for a specific column only if another criteria is not met.
so if Column Status does not equal Cancelled, Canceled, Closed, Done then put them in buckets of <30, <60, <180, <=365. here is what I have and it is failing. I do know that before I put the status criteria up there first things worked okay.
DOesn't work:
=IF[@[Status]] !="Cancelled, Canceled, Closed, Done), (IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", ))))))
Works:
=IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", )))))
Thanks in advance
=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed", [@[Status]]<>"Done"), IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", "")))),"")
Make sure to replace the range references ([@Status] and [@Days Open]) with the actual column references in your worksheet.
This formula checks if the Status column is not equal to "Cancelled", "Canceled", "Closed", or "Done". If the condition is true, it applies the time bucket calculation based on the Days Open column. If the condition is false, it returns an empty string. The text was created with the help of AI.
5 Replies
=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed", [@[Status]]<>"Done"), IF([@[Days Open]]<=30, "<30", IF([@[Days Open]]<=60, "<60", IF([@[Days Open]]<=180, "<180", IF([@[Days Open]]<=365, "<365", IF([@[Days Open]]>365, ">365", "")))),"")
Make sure to replace the range references ([@Status] and [@Days Open]) with the actual column references in your worksheet.
This formula checks if the Status column is not equal to "Cancelled", "Canceled", "Closed", or "Done". If the condition is true, it applies the time bucket calculation based on the Days Open column. If the condition is false, it returns an empty string. The text was created with the help of AI.
- Something like this is what I want todo
=IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed",(TODAY()-[@Created]),"Closed"))- =IF(AND([@[Status]]<>"Cancelled", [@[Status]]<>"Canceled", [@[Status]]<>"Closed"),
TODAY()-[@Created],
"Closed")
- So this might be stupid, what/how would I modify this to to check the status, and if neither of those I do a calculation of determine how long it is open. So if not Cancelled to do a simple Created-Today() calcuation.
