AUTOMATE PROGRESS BAR BASED ON MULTIPLE CONDITIONS
Hello, I am trying to automate my progress bar, basing it's progress on multiple conditions.
I have 10 columns I would like the progress bar to consider.
Example below of how i would like it to work:
If Cost 1 AND Hours 1 IS NOT Empty then progress = 25%
If Parts AND Survey AND time IS NOT Empty then progress = 50%
If Qualified AND Submit A
ND CBA IS NOT Empty then progress = 75%
If Case AND Log IS NOT Empty then progress = 100%
Please can I ask for some support on how I would go about writing this formula. I am used to Alteryx and Excel formulas but completely stumped on Smartsheet. Any help or advice would be hugely appreciated.
Equally if there is another way i can go about showing this progress in a more detailed way ie 10%,20%30% etc then i would welcome the suggestions!
Best Answer
-
The important thing to remember is to write your formula to solve for the 100% value first. Otherwise you'd always get a result of 10% because the formula will stop on the first true result which would be the first column being filled in. Here's a formula for 10% increments across all 10 columns:
=IF(LOG@row = 1, 1, IF(NOT(ISBLANK(CASE@row)), 0.9, IF(NOT(ISBLANK(CBA@row)), 0.8, IF(NOT(ISBLANK(SUBMIT@row)), 0.7, IF(QUALIFIED@row = 1, 0.6, IF(NOT(ISBLANK([TIME 1]@row)), 0.5, IF(NOT(ISBLANK(SURVEY@row)), 0.4, IF(NOT(ISBLANK(PARTS@row)), 0.3, IF(NOT(ISBLANK([HOURS 1]@row)), 0.2, IF(NOT(ISBLANK([COST 1]@row)), 0.1, 0))))))))))
Answers
-
Progress Bar formula:
=IF(AND(NOT(ISBLANK(CASE@row)), LOG@row = 1), "Full", IF(AND(QUALIFIED@row = 1, NOT(ISBLANK(SUBMIT@row)), NOT(ISBLANK(CBA@row))), "Three Quarter", IF(AND(NOT(ISBLANK(PARTS@row)), NOT(ISBLANK(SURVEY@row)), NOT(ISBLANK([TIME 1]@row))), "Half", IF(AND(NOT(ISBLANK([COST 1]@row)), NOT(ISBLANK([HOURS 1]@row))), "Quarter", "Empty"))))
Percent formula (make sure to format column as text/number and a percent):
=IF(AND(NOT(ISBLANK(CASE@row)), LOG@row = 1), 1, IF(AND(QUALIFIED@row = 1, NOT(ISBLANK(SUBMIT@row)), NOT(ISBLANK(CBA@row))), 0.8, IF(AND(NOT(ISBLANK(PARTS@row)), NOT(ISBLANK(SURVEY@row)), NOT(ISBLANK([TIME 1]@row))), 0.5, IF(AND(NOT(ISBLANK([COST 1]@row)), NOT(ISBLANK([HOURS 1]@row))), 0.2, 0))))
Also, your example sheet for the 75% bar didn't have a value in the CBA column which according to your criteria would be needed for a 75% result.
Since your conditions spanned 10 columns I considered each one as 10% when creating the formula for the Percent version. If you want different percentages just change the .2 to a .25 etc
-
Hi Mike, this is great thank you so much!
When I input the % formula into the sheet it goes up by 20%, 50%, 80%, 100% which is due to how initially explained. How would I go about amending this formula so that each column counts up in 10% incruments?
So, instead of Cost 1 and Hours 1 only changing to 20% if both conditions are met, can if Cost1 is not blank = 10% etc?
Hope this makes sense!
Thanks so much again :)
-
The important thing to remember is to write your formula to solve for the 100% value first. Otherwise you'd always get a result of 10% because the formula will stop on the first true result which would be the first column being filled in. Here's a formula for 10% increments across all 10 columns:
=IF(LOG@row = 1, 1, IF(NOT(ISBLANK(CASE@row)), 0.9, IF(NOT(ISBLANK(CBA@row)), 0.8, IF(NOT(ISBLANK(SUBMIT@row)), 0.7, IF(QUALIFIED@row = 1, 0.6, IF(NOT(ISBLANK([TIME 1]@row)), 0.5, IF(NOT(ISBLANK(SURVEY@row)), 0.4, IF(NOT(ISBLANK(PARTS@row)), 0.3, IF(NOT(ISBLANK([HOURS 1]@row)), 0.2, IF(NOT(ISBLANK([COST 1]@row)), 0.1, 0))))))))))
-
Thanks so much, this is exactly what i am after!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
