Progress bar

Melitta

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), " ")))))))

Paul Newcome

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")))

Paul Newcome

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")))))

Paul Newcome

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")))

Paul Newcome

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")))))

Melitta

Thanks @Paul Newcome

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

Paul Newcome

Happy to help. 👍️