Help! Expected Progress Formula #INVALID OPERATION

My project health column is based on Expected Progress column. Works great with the exception where there there's no data (blank cell) the formula returns #INVALID OPERATION

I've tried adding a IF(OR(ISBLANK but haven't been able to get it correct. Would appreciate help/guidance!. Thank you

=IF([% Complete]@row = 1, "Blue", IF(OR(([Expected Progress]@row - [% Complete]@row) < 0.1, [% Complete]@row >= [Expected Progress]@row), "Green", IF(AND(([Expected Progress]@row - [% Complete]@row) >= 0.1, ([Expected Progress]@row - [% Complete]@row) <= 0.25), "Yellow", IF(([Expected Progress]@row - [% Complete]@row) > 0.25, "Red", ""))))

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭

    Hello @AClark200,

    How would you like to handle the situations where the Expected Progress cell is blank? Would you like the formula to return a blank (e.g., "") as well? If so, would the following work?

    =IF([% Complete]@row = 1, "Blue", IF(ISBLANK([Expected Progress]@row]), "", IF(OR(([Expected Progress]@row - [% Complete]@row) < 0.1, [% Complete]@row >= [Expected Progress]@row), "Green", IF(AND(([Expected Progress]@row - [% Complete]@row) >= 0.1, ([Expected Progress]@row - [% Complete]@row) <= 0.25), "Yellow", IF(([Expected Progress]@row - [% Complete]@row) > 0.25, "Red", "")))))

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!