Formula revision to incorporate response for blank cells and add string

Miss_Priss

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.

Kelly Moore

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

@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

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

@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

@Miss_Priss

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