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 singleterm 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 singleterm 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
Check out the Formula Handbook template!