Seems basic, but I'm having a hard time calculating the expected progress vs actual progress

LauraLou

Hi Everyone,

I'm new to smartsheet and I'm having a hard time figuring out how to take the planned progress versus the actual progress in roll-up form.

For example, for planned progress (percentage), I have the following formula roll-up that I found in the community that looks to be actual:

=IF([Planned Start Date]@row > TODAY(), 0, IF([Planned End Date]@row < TODAY(), 1, (TODAY() - [Planned Start Date]@row) / ([Planned End Date]@row - [Planned Start Date]@row)))

So that column seems okay, but the actual progress doesn't feel accurate. The percentage actual complete tasks are manually entered subjectively. For example, if the planned start and planned end date ended and recorded 100%, while the actual date might match with planned, perhaps it hasn't ended yet, and we're behind but we feel it's only 25% actually complete.

As of now, I'm averaging the children using =AVG(CHILDREN()) of each parent task in the '% work complete' (actual) column that contains subtasks, averaging the subjective actual work progress. This seems okay, but misleading at the very top row..... The reasoning being, the top level actual start date matches the planned start date, and if we measure these against the planned end date they have the same value or it's saying the percent of work complete (actuals) is ahead if I average all the averages below the parent, but it's not accounting for the total duration days (190d).

Hope this makes sense! I see so many things the community has done that seem way more sophisticated then what I'm trying to accomplish. I'd love to hear what you think and any recommendations/suggestions you can offer me.

Thanks so much,

Laura

MichaelTCA

Hello @LauraLou,

Sorry I'm a little confused as to which column(s) are we actually working with here.

Are these sections in a hierarchy?

You could try the AVERAGEIF() function if you want to use additional criteria.

JamesB

@LauraLou

I am doing something similar with a helper column. The formula below lives in a column I named Task Target %. I also have a column named Is Parent.

Here is the formula for my Is Parent Column:

=IF(COUNT(CHILDREN([Program/ High Impact Projects]@row)) > 0, COUNT(ANCESTORS()) + 1)

This formula will place a number in the parent cells, but leave the children blank. Then in my Task Target %, I have the following formula:

=IF(ISBLANK([Is Parent]@row), IF(ISBLANK([Start Date]@row), 0, IF([End Date]@row <= TODAY(), 1, IF([Start Date]@row > TODAY(), 0, AVG(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))))), AVG(CHILDREN()))

To break this down, the formula first looks to see if the row is a parent.

IF True, it Averages the percent complete on the Children

IF False, then Check is Start Date is Blank

IF Start date is Blank = True, then 0%

IF Start date is Blank = False, then check if the end date is passed

IF End is in the past = True, set target %100

IF End is in the past = False, check is start date is in the future.

IF Start date is in the Future = True, set target % to 0.

IF Start date is in the Future = False, Then AVG the percent that should be complete from Start Date to Todays Date, based on the total number of days for the task.

You can then track this percent against your actual % Complete.

LauraLou

Ah, this looks interesting.. I'll test out your suggestion to see how it plays, it might be what I'm looking for. I met with my fellow PMs and we were thinking perhaps we needed a "Weight" column, which might be heading toward what your helper column is doing.

I'll give it try!

Thanks for sharing :)