Formula revision to incorporate response for blank cells and add string

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!