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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!