Progress Bar Formula
Hello!
I am trying to create a formula for the progress bar which will change according to the state and % complete. The following formula is what I have come up with but it doesn't not seem to be working. Is IF/And required here?
Debbie
Best Answer

@Deborah Berwaldt , my bad, I missed the @row needed at the end of the [% Complete] column.
Give this a try:
=IF(AND(State@row = "Blue", [% 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 = "Blue", [% Complete]@row = 1),"Full")))))
One thing I did spot (likely a business logic point) is the combinations of state and % complete appears to have some gaps. For example, if state is blue and % complete is 50% then the progress column won't show anything, but that may not be a valid combination in your world.
Let me know if that covers the conditions you're looking for.
Thanks!
Answers

I hope you're well today.
At first glance, it appears that the first condition in the IF statement is missing the AND wrapper that the rest have:
=IF(AND(State@row = "Blue",[% Complete]=0), "Empty"......
I hope that helps. Let me know if that achieves the desired result.
Thanks,
Sing

@Sing C Thanks for your thoughts on this but unfortunately the formula is still not working. Any other suggestions?

@Deborah Berwaldt , my bad, I missed the @row needed at the end of the [% Complete] column.
Give this a try:
=IF(AND(State@row = "Blue", [% 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 = "Blue", [% Complete]@row = 1),"Full")))))
One thing I did spot (likely a business logic point) is the combinations of state and % complete appears to have some gaps. For example, if state is blue and % complete is 50% then the progress column won't show anything, but that may not be a valid combination in your world.
Let me know if that covers the conditions you're looking for.
Thanks!

Thank you! This worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!