Adding If Not Blank to Existing Formula

Options
Kaleb
Kaleb ✭✭✭✭
edited 02/08/23 in Formulas and Functions

Smartsheet Community:

Do I Add If Is Not Blank to Each Part of My Current Formula or Just at the Beginning?

Current Formula:

=IF(OR(CPI@row = 1, CPI@row <= 0.95), "Green", IF(OR(CPI@row = 0.85, CPI@row <= 0.94), "Yellow", IF(OR(CPI@row = 0, CPI@row <= 0.84), "Red")))

Attempted Modified Formula But Incorrect Response Results:

=IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 1, CPI@row <= 0.95), "Green", "", IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 0.85, CPI@row <= 0.94), "Yellow", "", IF(OR(NOT(ISBLANK(CPI@row)), CPI@row = 0, CPI@row <= 0.84), "Red", "")))

Thanks

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Kaleb

    I think this formula is what you're after:

    =IF(NOT(ISBLANK(CPI@row)), IF(OR(CPI@row = 1, CPI@row >= 0.95), "Green", IF(OR(CPI@row >= 0.85, CPI@row >= 0.94), "Yellow", IF(OR(CPI@row = 0, CPI@row <= 0.84), "Red"))), "")

    Output:

    If I've misunderstood something, let me know. If not, I hope this helps! 😊

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!