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
-
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
Categories
Check out the Formula Handbook template!