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.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!