Common formatting of Percent Complete.

Hi everyone - I like to format my rows so that things that are complete are green and things that are late are red.

So I created some conditional formatting so that if a task was 100% complete then it was green and if it had a due date in the past and was not 100% complete it was red.

I was pretty happy until I added some parent rows.

Parent rows measure completeness from 0-1 not 0-100.

So I added to both 100 and 1 as the values that would turn a row green.

Does it seem odd to other people that children are measured on a 0-100 scale and parents on a 0-1 scale?

Find more posts tagged with

Conditional Formatting

Comments

Travis

Hi Todd, parent and child rows both use the same calculations for percent complete. 1 = 100%, .33 = 33%, etc.

I tested this in a sheet with your rules and it is working as expected. This first screenshot contains your two rules. Green if % Complete is 100% (1) and red if the End Date is in the past and % Complete is not 100% (1). Both the child rows and the parent rows are formatted with the same rules, where 1 = 100%:

Make sure you are using "is equal to" when defining percent complete in your formatting rules. If you are still having trouble, post a screenshot of your sheet, showing me your formatting rules and how child rows are formatted but the parent rows are not.

Screen Shot 2015-07-21 at 9.48.28 AM.png

Screen Shot 2015-07-21 at 9.48.58 AM.png

Todd Malone

Hey Travis - thanks for the awesome reply.

Everything looks grand in your example but here's what mine looks like:

The parent is using a 0-1 scale and the children 0-100.

I couldn't find an easy way to change the nubmer format.

Cheers...Todd

Travis

Thanks for the screenshots. Your % Complete column is not formatted as a percent. This means your child rows are 10,000% complete (100 = 10,000%) but parent rows only rollup child values up to 100%. I cannot see the rest of your children, but I am guessing there is at least one that does not have a percent complete value, which is likely why the parent rollup is .77 (77%).

Single left click on the % Complete column header (which will select the entire column) and press the Percent Format button on your toolbar. You will notice your child rows will become 10,000% and your parent row will become 77%. Now try building your formatting rules based on 1 = 100%.

Todd Malone

Ahh - the percen tool! That was what I needed.

It's all good now.

Thanks for the super-clear instructions.

Michele Deo

So I got a new twist to this. I have a template setup that I have formatted the % Complete as a percentage column. When I key in 100 in the % complete column it comes out as 10,000%, but going to another user when they open the smartsheet and key in 100 it shows it as 100% complete. Why the different results? I'm the Owner of the sheet, and the other is an Admin of the sheet.

So my status column doesn't update on the Owner view, but does in the Admin view - snaps below. why is the view different for each user? What setting do I need to switch on the Owner view to see the same of what the Admin is seeing?

Admin shot.png

Owner Snap.JPG

Alex Palmer

We are having a similar issue with one of our users and we can't seem to figure out why. Any updates?

Michele Deo

No I haven't heard from anyone at Smartsheet yet to determine what the issue is. It is still occuring.