Progress bar
Hello, We typically use % complete but I've been asked about using the progress bar rather than % complete.
I have my formulas below working however, if you have tips on how to shorten them, simplify them, please let me know  Thanks!
I'd like the project managers to set the State (drop down symbols RYGB) and the % Complete (drop down  0, .25, .50,.75, 1).
A formula will fill in Status (not started/in progress/completed) based upon the state (RYGB) and % Complete:
=IF(AND(State@row = "Blue", [% Complete]@row = 1), "Complete", IF(AND(State@row = "Green", [% Complete]@row = 1), "% ERROR", IF(AND(State@row = "Yellow", [% Complete]@row = 1), "% ERROR", IF(AND(State@row = "Red", [% Complete]@row = 1), "% ERROR", IF(AND(State@row = "Blue", [% Complete]@row < 1), "% ERROR", IF(AND(State@row = "Green", [% Complete]@row = 0), "Not Started", IF(AND(State@row = "Yellow", [% Complete]@row = 0), "Not Started", IF(AND(State@row = "Red", [% Complete]@row = 0), "Not Started", IF(AND(State@row = "Green", [% Complete]@row > 0), "In Progress", IF(AND(State@row = "Yellow", [% Complete]@row > 0), "In Progress", IF(AND(State@row = "Red", [% Complete]@row > 0), "In Progress")))))))))))
Another formula for the progress bar based upon the state (rygb) and the % complete:
=IF(State@row = "Blue","Full", IF(AND(State@row = "Green", [% Complete]@row = 0), "Empty", IF(AND(State@row = "Green", [% Complete]@row = 0.25), "Quarter", IF(AND(State@row = "Green", [% Complete]@row = 0.5), "Half", IF(AND(State@row = "Green", [% Complete]@row = 0.75), "Three Quarter", IF(AND(State@row = "Green", [% Complete]@row = 1), " ",IF(AND(State@row = "Yellow", [% Complete]@row = 0), "Empty",IF(AND(State@row = "Yellow", [% Complete]@row = 0.25), "Quarter", IF(AND(State@row = "Yellow", [% Complete]@row = 0.5), "Half",IF(AND(State@row = "Yellow", [% Complete]@row = 0.75), "Three Quarter", IF(AND(State@row = "Yellow",[% Complete]@row = 1)," ",IF(AND(State@row = "Red", [% Complete]@row = 0), "Empty", IF(AND(State@row = "Red", [% Complete]@row = 0.25), "Quarter", IF(AND(State@row = "Red", [% Complete]@row = 0.5), "Half", IF(AND(State@row = "Red", [% Complete]@row = 0.75), "Three Quarter", IF(AND(State@row = "Red", [% Complete]@row = 1), " ")))))))
Best Answers

Lets give this a try...
=IF([% Complete]@row = 1, IF(State@row = "Blue", "Complete", "% ERROR"), IF(State@row = "Blue", "% ERROR", IF([% Complete]@row = 0, "Not Started", "In Progress")))

And here is the second one...
=IF(State@row = "Blue", "Full", IF([% Complete]@row = 0, "Empty", IF([% Complete]@row = .25, "Quarter", IF([% Complete]@row = .5, "Half", IF([% Complete]@row = .75, "Three Quarter")))))
Answers

Lets give this a try...
=IF([% Complete]@row = 1, IF(State@row = "Blue", "Complete", "% ERROR"), IF(State@row = "Blue", "% ERROR", IF([% Complete]@row = 0, "Not Started", "In Progress")))

And here is the second one...
=IF(State@row = "Blue", "Full", IF([% Complete]@row = 0, "Empty", IF([% Complete]@row = .25, "Quarter", IF([% Complete]@row = .5, "Half", IF([% Complete]@row = .75, "Three Quarter")))))

Thanks @Paul Newcome
I appreciate your responses and helping me in how to think about my formulas so I can simplify them.

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!