Automate the Progress Bar

lisarae723

Hi there Smartsheet Guru's!

Im trying to figure out the best way to automate a progress bar, based of 9 columns that all have check boxes. Screenshot shown: 

Jeff S

Here's an idea I just tried out that might work for you: You can automate the progress bar using a formula in conjunction with the progress status symbol column type. You can add a column that calculates the percentage completion of the nine tasks, called "% Complete". The formula for the first row would be:

=COUNTIF([TLP Review]1:[Customer Submits Final Drawing]1,1)/9

Then, you can reference this "% Complete" column in your progress column, which should be a symbol column with the progress bar status type selected. You can use the following formula:

=IF([% Complete]1 < 0.25, "Empty", IF([% Complete]1 < 0.5, "Quarter", IF([% Complete]1 < 0.75, "Half", IF([% Complete]1 < 1, "Three Quarter", "Full"))))

This will select the appropriate progress bar level for the percent complete - "Empty" if the % complete is between 0 and .25, "Quarter" for .25 to .5, etc. You can then hide the "% Complete" column if needed.

Paul Newcome

I would suggest using a COUNTIFS statement within a nested IF statement. Something along the lines of

 

=IF(COUNTIFS([TLP Review]@row:[Customer Submits Final Drawing]@row, 1) < 1, "Empty", IF(COUNTIFS([TLP Review]@row:[Customer Submits Final Drawing]@row, 1) < 3, "Quarter", IF(COUNTIFS([TLP Review]@row:[Customer Submits Final Drawing]@row, 1) < 6, "Half", IF(COUNTIFS([TLP Review]@row:[Customer Submits Final Drawing]@row, 1) < 9, "Three Quarter", "Full"))))

 

This will give you the following results depending on how many boxes are checked (feel free to adjust the numbers as desired):

 

0 = Empty

1 = Quarter

2 = Quarter

3 = Half

4 = Half

5 = Half

6 = Three Quarters

7 = Three Quarters

8 = Three Quarters

9 = Full

lisarae723

This is it! I would still be fumbling with the formula. I was using countif not countifs and, I wasnt using  @row. It works perfectly. Thank you!

 

 

Paul Newcome

Happy to help. I personally never use COUNTIF anymore. COUNTIFS works even with the one set of criteria, and I can just plug any extras in as needed without having to worry about forgetting to put the "S" on there.

 

@row is super useful in MOST cases. The only time I specify a row number anymore is when I need to reference a different row. It makes a lot of things so much easier to include testing and whatnot.

alp-19

Hey Paul - Trying to apply your logic to this issue. Can you tell where I fumbled?

 

I'm trying to create a % formula that will calculate the % complete of task / row based on the number of checkboxes that have been checked.

For example- I used 7 checkboxes for 7 task rows:

0 = Empty

1 = Quarter

2 = Quarter

3 = Half

4 = Half

5 = Three Quarters

6 = Three Quarters

7 = Full

 

**I tried below harvey ball formula and rec'd an "unparseable" error....

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 1, "Empty”, IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 3, "Quarter”, IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 6, "Half”, IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 9, "Three Quarter", "Full"))))

 

Thank you in advance.

 

~Amy

Paul Newcome

You say "7 task rows". Is it actually 7 rows or is it 7 columns going across the row?

 

Other than that, your syntax is correct and your column names/ranges are consistent. Without a screenshot, that would be my first question.

 

Should your ranges be

7 checkbox columns across the same row?

[First Check Column]@row:[Last Check Column]@row

.

or should it be 7 checkboxes down the same column?

[Check Column]1:[Check Column]7

alp-19

Here is a screenshot....might clarify.  Thanks for your help. I'm running around in circles.

Paul Newcome

That's really odd. Everything appears to be absolutely correct.

 

Lets try this... Take the next four cells below it, and enter each individual IF statement into it's own row. If only one throws an error then it narrows down where the issue could be. If they all throw an error, we may need to break it down even further or start thinking a little outside of the box.

alp-19

Ok- the first 3 statements did not work, but the last one DID -

 

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 9, "Three Quarter", "Full")

 

Recommendations on putting them together? I must be missing something simple.

 

TY again!

 

Paul Newcome

So this is what you have...

 

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 1, "Empty”)

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 3, "Quarter”) 

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 6, "Half”)

=IF(COUNTIFS([Action 1]@row:[Action 7]@row, 1) < 9, "Three Quarter", "Full")

.

and only the last one is working?

Temporarily change the column type to text/number and just do the COUNTIFS in a row.

.

=COUNTIFS([Action 1]@row:[Action 7]@row, 1)

.

Check random boxes within that row to make sure the COUNTIFS is working.

 

You can even drag this down the rows and have each row a different number of boxes checked.

 

Paul Newcome

If the COUNTIFS is what is failing, try changing the criteria to true.

 

=COUNTIFS([Action 1]@row:[Action 7]@row, true)

alp-19

Thanks Paul - I appreciate your time and thinking!

Paul Newcome

Sure thing! Did this solve the issue?

Miranda Rais

@Paul Newcome - I want to do something really similar. I want to COUNTIF the cells in each column are green 'Checked Ok' and adjust the progress bar based on the number of these. Any help would be very gratefully received! ty

Paul Newcome

@Miranda Rais You are going to first use a COUNTIFS to count how many meet your criteria then use that as a part of the "logical statement" portions within a nested IF statement.

=IF(COUNTIFS([We View]@row:[Sports App IOS]@row, "Checked Ok") = 0, "output_if_zero", IF(COUNTIFS([We View]@row:[Sports App IOS]@row, "Checked Ok") = 1, "output_if_one", IF(COUNTIFS([We View]@row:[Sports App IOS]@row, "Checked Ok") = 2, "output_if_two", ...............................

You are going to need to change the "output_if_#" portions to output whatever you want for each number and continue the pattern until you have an output for each of the different possible counts.