Formula revision to incorporate response for blank cells and add string
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
-
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
-
@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:
-
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
-
@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. ❤️
-
I'm doing the happy dance too. 🙆♀️ (they need a good emoji for that). I'm glad it worked for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!