Summary Bar Progress in % different from time based calculated one

HolgerS

Hi, at present I am trying to calculate the necessary progress of the project on each bar by using the days between start, end and today(). Then comparing that result with the entered % of completion.
Depending on % calculated compared to the entered % in (finished) it works well (=(NETWORKDAY([Start/ Start]@row; TODAY())) / ([Dauer/Duration]@row) )
and colour can be set. Row P-Soll and TF-Check (Time Ok, F (==progress) OK…)

This works up to that situation where I create a summary bar. This function works not with the summary bar and I am confused why. How smartsheet calculates the progress in % of the summary bar, of is there a mistake for my own calucated time based progress in % ?

jmyzk_cloudsmart_jp

Hi @HolgerS

When dependencies are enabled on a project sheet,

The % Complete on the parent row calculates a weighted percentage based on the Duration and % Complete entered on each child row. In this way, completing child rows with shorter durations doesn't impact the parent row % Complete as much as completing a longer duration child row.

The logic of the calculation is that, for example, if child 1 task has a 1-day duration and is 100% complete, and child task 2 has 100 days and 50%, we want to put more weight on the longer one.

https://help.smartsheet.com/articles/765753-parent-rollup-functionality

So, if you want your formula to work even for the parent or summary row, change the % complete setting. This setting decides which column will show the completion percentage in the Gantt chart.

HolgerS

Hi jmyzk_cloudsmart_jp,

thanks for help. Unfortunately this want solve my issue. By evaluating the progress on the summary smartsheet shows that the project is behind track even all childs are in time.

If have similar the same issue like there (dont consider the coloring)

https://community.smartsheet.com/discussion/76028/formula-to-alert-red-green-yellow-comparing-progress-to-expected-complete

The difference is only, that the expected % is not directly entered. I calculate the expected value based on date(s)by using the a.m. formula as difference between start-date, today and finish date and duration.

Is Smartsheet doing something different with that %?

jmyzk_cloudsmart_jp

Hi @HolgerS

I hope I understand your problem correctly.

In the image below, the column, [Networkday / Duration] or [Start to Today(Net)] / [Start to End (Duration)], has essentially the same formula as yours. (The formula is a cell formula)

I copied the calculated value manually in the % Complete column, which I use to show the % complete shade in the Gantt bar. (This is because you can not use formulas in a cell of a column designated as % Complete column in the project setting)

Then, in the following sheet below, which I copied from the sheet above, I changed the % Complete column in the project setting to the [Networkday / Duration] column, which contained a cell formula. (You can not select a column with a column formula in the project setting.)

Then, all the cell formulas in the column were lost, and the duration weighted % complete was assigned to the parent or summary row, as I explained in the previous comment.

https://app.smartsheet.com/b/publish?EQBCT=b71e74e8070f432e8201f17266170c12

Before % Complete setting change

After % Complete setting change

To avoid manually entering the % complete, I created a workaround that created links from another sheet.
As shown in the image below, I created cell link-ins to the Task 1 to 4's % Complete column, which I set in the project settings. Though you can not use a formula in the column, you can set the value by cell linking.
I am unsure if I can call this a workaround, as it is not feasible to maintain the same Primary, Start, End, and Duration in both sheets.