Difficulties Nesting formula

Austin Kuhn
Austin Kuhn ✭✭✭
edited 11/20/20 in Formulas and Functions

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

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Austin Kuhn
    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
    Kelly Moore ✭✭✭✭✭✭
    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

  • Austin Kuhn
    Austin Kuhn ✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!