AUTOMATE PROGRESS BAR BASED ON MULTIPLE CONDITIONS

Lauren Holmes

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!

Mike TV

@Lauren Holmes

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

Mike TV

@Lauren Holmes

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

Lauren Holmes

https://community.smartsheet.com/discussion/comment/342180#Comment_342180

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

Mike TV

@Lauren Holmes

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

Lauren Holmes

@Mike TV

Thanks so much, this is exactly what i am after!