Difficulties Nesting formula

Austin Kuhn

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"

Capture.PNG

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

Kelly Moore

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

Mark Cronk

=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

Austin Kuhn

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

Kelly Moore

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

Austin Kuhn

https://community.smartsheet.com/discussion/comment/269615#Comment_269615

This did it! Thanks Kelly. Will use this formula to learn. Do appreciate your time and help! Have a good weekend.