Progress Bar Roll-up

PR

Hello,

Is there a way for the progress bar of the children rows to roll up to a parent roll where it would display the average in the progress bar form? 

As you can see in the screenshot, the progress bars are updated with the status of a line item through a formula.

Thank you for your help! 

Paul Newcome

Ok. You will want to create a table where each bar value has a numeric value.

.

Bar                  Number

Empty                    0

Quarter                  1

Half                        2

Three Quarters      3

Full                         4

.

You would then create a helper column that establishes a number for each row.

.

Score

   f1

   f1

   f1

   f1

.

f1:

=INDEX(Number:Number, MATCH([Status Bar]@row, Bar:Bar, 0))

.

You would put this in each of the child rows. In the parent rows, you would average the children and then either round up or down based on your preference. I personally just use the integer which would be the same as rounding down to the nearest whole number.

 

=INT(AVG(CHILDREN()))

.

Then in the parent rows of the [Status Bar] column, you would use this...

 

=INDEX(Bar:Bar, MATCH(Score@row, Number:Number, 0))

 

to convert the average back into the corresponding Status Bar text.

Paul Newcome

How would you want it rolled up? Averaged, Highest value, Lowest value, other, etc?

PR

Taking the average would be the best! 

Paul Newcome

Ok. You will want to create a table where each bar value has a numeric value.

.

Bar                  Number

Empty                    0

Quarter                  1

Half                        2

Three Quarters      3

Full                         4

.

You would then create a helper column that establishes a number for each row.

.

Score

   f1

   f1

   f1

   f1

.

f1:

=INDEX(Number:Number, MATCH([Status Bar]@row, Bar:Bar, 0))

.

You would put this in each of the child rows. In the parent rows, you would average the children and then either round up or down based on your preference. I personally just use the integer which would be the same as rounding down to the nearest whole number.

 

=INT(AVG(CHILDREN()))

.

Then in the parent rows of the [Status Bar] column, you would use this...

 

=INDEX(Bar:Bar, MATCH(Score@row, Number:Number, 0))

 

to convert the average back into the corresponding Status Bar text.

PR

Thank you, Paul.

I apologize for struggling. I was able to create the numeric value for the progress bars and I created the helper column. But I' struggling with the Score column. Please see the snapshot below. What am I entering incorrectly? 

 

Thank you so much for your patience and help.

Paul Newcome

Take a look at my below Screenshot. Maybe that will help clarify things...

PR

Oh wow, wouldn't have gotten that. It worked! Thank you so much for your help! 

Paul Newcome

Happy to help! 

 

Sorry for the initial confusion.

acstonebraker

Hey Paul,

I'm hoping you can also help me do the same thing! I'm trying to follow your instructions, using your screenshot as a guide, but I seem to be getting errors still. Apologies in advance if I'm missing something elementary here – I only started using Smartsheet a few months ago and have been self-training. :)

Maybe I misunderstood your screenshot, but I added in f1 to rows 3-5 in the "Score" column, put f2 in row 2 of the "Score" column, and then have f3 in row 2 of my "% Complete" column.

I'm not sure I fully understand the syntax of each formula, so there may be some errors there?

Any help you can provide would be much appreciated!

Paul Newcome

The problem is in the formula that initially converts the bars to numeric values. You do not have a column called "Progress". You would need to change these column names to the names of the columns you have in your sheet. Based on your screenshot, I would suggest replacing

Progress

with

[% Complete]

in each instance of the formulas.

acstonebraker

OF COURSE! Wow, thank you so much. That definitely worked.

The new problem I'm having is that the AVERAGE(CHILDREN()) formula doesn't seem to want to work. I've tried adding in the Task Name so it read AVERAGE(CHILDREN[Task Name])), with of course the real name of the task being inserted, but that didn't seem to work either. I've also just tried doing AVERAGE(Score3:Score5) and it still said #UNPARSEABLE.

What else am I doing wrong here?

Paul Newcome

Try

=AVG(CHILDREN())

acstonebraker

Beautiful, that also worked. (Should've thought of that...!)

Of course I'm also now having issues with f3, getting #NO MATCH in the parent row for my progress bar. I feel like this has to do with the fact that the average of my cells isn't actually a whole number. I've moved the decimal over so it looks like one in the Score column, but is there something else I should do so that the formula recognizes this number as a whole number, and therefore will accurately match the score number to the progress bar?

UPDATE: My suspicion is confirmed! I edited the Statuses for one of my line items, making the average a true whole number, and the parent row progress bar worked like a charm!

Is there a trick to making this work every time, even if the average isn't a whole number? I've tried looking up how to format the columns and came up empty.

Your continued insight and assistance are greatly appreciated!

Paul Newcome

=ROUND(AVG(CHILDREN()))

This will round to the nearest whole number.

acstonebraker

Worked like a charm!! THANK YOU SO MUCH for all of your help! I really and truly appreciate you walking me through this literally step by step.

Hope you had a happy holiday and wishing you a Happy New Year!

Paul Newcome

No worries. Happy to help! 👍️

Please do not forget to mark the answer that best fit your needs as the "Accepted Answer". That way others looking for a similar solution know that information can be found.