Formula revision to incorporate response for blank cells and add string

Options
Miss_Priss
Miss_Priss ✭✭
edited 04/09/24 in Formulas and Functions

Hello!

The AI generator has failed me on this one and I need some help to revise my current formula. I'm almost there!

This formula below exists in the *Current LO# column. It is referencing the LO# column and providing a response based on the cell with a few caveats.

1) If the LVL is = 1 it provides only the number in the LO# column, unless the LO# cell is blank, then it provides a "0"

This part is working properly:

2) For all responses where the LVL is greater than 1, the LO# is provided and "of" is added with the corresponding threshold details

The issue I'm experiencing is when the LVL is greater than 1 and the LO# cell is blank, I'm getting a "0" but it's not including the additional string as outlined: + " of " + IF(LVL@row = 2, "3", IF(LVL@row = 3, "20", IF(LVL@row = 4, "20", IF(LVL@row = 5, "50"))))))))

This is a screenshot of the issue.

If the LO# cell is blank and the LVL is greater than 1, I want it to include a "0" plus the string outlined.

This is the entire formula as it is now:

=IF(ISBLANK([LO#]@row), "0", IF(LVL@row = 1, [LO#]@row, IF([LO#]@row <> "", [LO#]@row + " of " + IF(LVL@row = 2, "3", IF(LVL@row = 3, "20", IF(LVL@row = 4, "20", IF(LVL@row = 5, "50"))))))))

I'm hoping someone knows what to adjust to fix this. I can't seem to set it up properly in the AI generator to crack it.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Miss_Priss

    Try this

    =IF(AND(ISBLANK([LO#]@row), ISBLANK(LVL@row)), "0", IF(LVL@row = 1, [LO#]@row, IF(LVL@row > 1, IF(ISBLANK([LO#]@row), "0", [LO#]@row) + " of " + IF(LVL@row = 2, "3", IF(LVL@row = 3, "20", IF(LVL@row = 4, "20", IF(LVL@row = 5, "50")))))))

    Will this work for you?

    Kelly

  • Miss_Priss
    Options

    @Kelly Moore That's almost it!

    That solved the current issue but it removed the "0" response for LVL 1 when a blank is present. Is it even possible to have it provide just a "0" if it is LVL 1 or is that too many arguments in the formula?

    This was corrected:

    But then this happened for LVL 1:


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Miss_Priss

    How's this

    =IF(OR(AND(ISBLANK([LO#]@row), ISBLANK(LVL@row)), AND(LVL@row = 1, [LO#]@row = "")), "0", IF(LVL@row = 1, [LO#]@row, IF(LVL@row > 1, IF(ISBLANK([LO#]@row), "0", [LO#]@row) + " of " + IF(LVL@row = 2, "3", IF(LVL@row = 3, "20", IF(LVL@row = 4, "20", IF(LVL@row = 5, "50")))))))

    Kelly

  • Miss_Priss
    Options

    @Kelly Moore That was it! I'm doing a happy dance right now in my seat. 😁

    Thanks for helping me solve that. It was such a headache trying to sort it out on my own. ❤️

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    @Miss_Priss

    I'm doing the happy dance too. 🙆‍♀️ (they need a good emoji for that). I'm glad it worked for you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!