Difficulties Nesting formula
Hello all,
I think I am close to the answer here but struggling to figure out what I need to add to the formula to. Looking for the formula guru's here. Here's what I need this formula to do. I think I am on the right path, hopefully.
- If "Lab Date Received" row is empty, return "Awaiting FER in Lab"
- If "Lab Start Date" is not empty AND "Lab Complete Date" is empty, return "In Process"
- If "Lab Start Date" AND "Lab completed date" row are not empty, return "Complete"
Do appreciate your time, and should we get an answer I will reverse engineer the appropriate formula to continue to learn. Appreciate your help.
Austin Kuhn
Best Answer
-
Hi Austin
Give this a shot.
=IF([Lab Received Date]@row = "", "FER Awaiting in Lab", IF([Lab Start Date]@row ="", "Pending", IF(AND([Lab Start Date]@row <> "", [Lab Date Completed]@row = ""), "In Progress", "Completed")))
In your screenshot your formula refers to "Pending" but I didn't see that requested above - so I added the "Pending" with what I thought you meant. We can easily tweak this as I probably misinterpreted your needs.
You were heading in the right direction with your formulas. You do have to be aware, since a Nested If stops at the first 'True' it encounters, of the order of the IFs. What that order is dependent on the will be dependent on the conditions that are needed, but in general, I typically look at my single-term IFs first before I look to add IFs containing AND or ORs. The absolute rule of what to drop in first however will always be, 'it depends'. 😉
Kelly
Answers
-
=IF(ISBLANK([Lab Date Received]@row), "Awaiting FER in Lab", IF(AND(NOT(ISBLANK([Lab Start Date]@row)),ISBLANK([Lab Complete Date]), "In Process", IF(AND(NOT(ISBLANK([Lab Start Date]@row)),NOT(ISBLANK([Lab completed date]@row)), "Complete","")
May need to add an IFERROR to the start.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks for the reply Mark, I definitely wasn't as close as I thought I was. Lot's of learning to do on formulas and nesting formulas. The formula above is still giving me an "incorrect argument set". I've since changed a few of the column titles to be more consistent across the sheet. Here is the formula I have now which is giving me Incorrect Argument Set.
If you can provide an email I'll share the sheet with you there is nothing confidential on it currently. Appreciate your time and help.
Thanks,
Austin Kuhn
-
Hi Austin
Give this a shot.
=IF([Lab Received Date]@row = "", "FER Awaiting in Lab", IF([Lab Start Date]@row ="", "Pending", IF(AND([Lab Start Date]@row <> "", [Lab Date Completed]@row = ""), "In Progress", "Completed")))
In your screenshot your formula refers to "Pending" but I didn't see that requested above - so I added the "Pending" with what I thought you meant. We can easily tweak this as I probably misinterpreted your needs.
You were heading in the right direction with your formulas. You do have to be aware, since a Nested If stops at the first 'True' it encounters, of the order of the IFs. What that order is dependent on the will be dependent on the conditions that are needed, but in general, I typically look at my single-term IFs first before I look to add IFs containing AND or ORs. The absolute rule of what to drop in first however will always be, 'it depends'. 😉
Kelly
-
This did it! Thanks Kelly. Will use this formula to learn. Do appreciate your time and help! Have a good weekend.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
