% Complete - how is it calculated?

LinusLy

How is the % Complete column calculated? In my screenshot, it shows 96%. I have 29 rows of data, 17 rows has the % Complete column populated with 100%. I thought % Complete only picks up data in the indented rows below Removal of Never Expires, but I must be wrong. Copying the info into an Excel spreadsheet and running the data through some simple formula, my % Complete should be 59. 17 rows have the dates for Start and Finish, so that left 12 to be done, so 59% is more like it. 96% makes it look like we are almost done.

Paul Newcome

Did you also enter data into the start/finish/primary columns? Do you have dependencies enabled?

Sandra Guzman

Hi @LinusLy!

I think the biggest thing that you need to understand is that the % Complete roll up is based on several data points. If you have the dependencies turned on, it will look at the duration of time and calculate the % based on a weighted average. This has caused confusion with my team since I haven't been able to clearly determine how the deconstruct the formula that is working behind the scenes.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

If you are simply trying to find an average, you could probably do so using formulas but that is now going to add manual work for you or your Admin.

There are quite a few community posts on this topic that you may want to browse. Personally, I would like the option of being able to choose whether or not I want a weighted average or a simple average calculation for this. You can always submit an enhancement request via the enhancement suggestion form.

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?origin=help&_ga=2.118895438.697653857.1634739995-1641732205.1634737294

All the best,

Sandra

Paul Newcome

It excludes blanks from the calculations.

LinusLy

Thank you, Paul, but I need more info from you. Which column is taken into account to make the calculation for the % Complete column? It should be the % Column, right? I went back to the column and entered 0% for all the blanks, such as that In Progress row in the screenshot. With more rows included, % Complete should be lower, but nothing happened. I saved the sheet, did a refresh with the browser. Still 96%.

Paul Newcome

Did you also enter data into the start/finish/primary columns? Do you have dependencies enabled?

LinusLy

I found out that the Start/Finish columns have to have data to be considered non-blank, and thus included in the count. I find that to be a strange requirement, because the way I see it if something has a Start Date and a Finish Date then it is already 100% Complete. But it is necessary to enter data into Start Date, Finish Date, AND % Complete, only then the Summary Value is calculated.

I am not aware of any Dependencies enabled.

Paul Newcome

Yes. That makes sense. The dates are assumed to be when the task is planned to occur.

LinusLy

Smartsheet-headache-6-percent.png

Smartsheet-headache-85-percent.png

Hello again, folks!

The issue of how %Complete is calculated continues to frustrate me.

I understand that for a line to be counted in %Complete it has to have Start and Finish populated. It seems those dates have to fit some other criteria, else the %Complete is all messed up.

As the two pictures shown, I have 10 items. The Count is correct. I have 3 of the 10 items 100% Completed, so I would expect %Complete to be 30%. If I make the Finish Dates for the other 7 far into the future, %Complete is 6% . If I make the Finish Dates something that is already past, I have 85% .

This block of rows is at the bottom of a big list of various other tasks. I thought the top row in the block is only affected by what's below it, but is there something out there that affects %Complete? What dates should I enter to get 30%.

Sandra Guzman

Hi @LinusLy!

I think the biggest thing that you need to understand is that the % Complete roll up is based on several data points. If you have the dependencies turned on, it will look at the duration of time and calculate the % based on a weighted average. This has caused confusion with my team since I haven't been able to clearly determine how the deconstruct the formula that is working behind the scenes.

https://help.smartsheet.com/articles/765737-project-sheet-columns-start-date-end-date-duration-complete-and-predecessors

If you are simply trying to find an average, you could probably do so using formulas but that is now going to add manual work for you or your Admin.

There are quite a few community posts on this topic that you may want to browse. Personally, I would like the option of being able to choose whether or not I want a weighted average or a simple average calculation for this. You can always submit an enhancement request via the enhancement suggestion form.

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?origin=help&_ga=2.118895438.697653857.1634739995-1641732205.1634737294

All the best,

Sandra

LinusLy

Thank you @Sandra Guzman . I have no access to muck around with the sheet's settings, so for now I just won't rely on %Complete , which is a shame that something I thought should be simple to use is so convoluted. I submitted an enhancement suggestion.