How to calculate actual % and Planned %: Comparing with columns, start date, end date, baseline star

Som

How to calculate actual % and Planned % : Comparing with columns { Start date, End date, Baseline start, Baseline End date }

Som

Paul Newcome can you please help ?

MichaelTCA

A planned % would have to be an input, in my mind, unless you have a function that checks conditions, such as IF, SUMIF, or COUNTIF, then have preset %'s to reference, or within the function itself. This is not a dynamic process.

If you want a (more dynamic) calculated planned % you will need to know more criteria; possibly an average % based on those criteria from previous tasks to have a somewhat accurate "estimate per task". A calculated % has to be numbers, so depending on what you use for a baseline, you may need a helper column to differentiate what you are comparing. Using the functions mentioned above will help in this process as well.

Then, obviously, your Actual "variable" / Planned "variable" = Actual %

For example: I set up meetings before projects to determine the estimate time/cost prior to starting anything. Then calculate a % of time used, based on what employees enter for labor every day. This information is presented in a report.

Actual Time / Estimate Time = % Time Used / Project

Som

https://community.smartsheet.com/discussion/comment/355733#Comment_355733

The actual % we can input manually based on the actual date, can you help me with planned %?

MichaelTCA

Hello,

A dynamic estimate % would require formulas to develop the % and possibly use helper columns to reference text as numbers.

An idea I have is to calculate the comparison between customers looking for information. Then take the average of how often the customer uses that information, and use that as an estimate %.

=AVG(Info Asked For Quantity/Total Requests)

The variables you mentioned in the post could look like this in a function:

=(Baseline End - Baseline Start)/(End Date - Start Date)

This function will evaluate the change in the baseline over the change in time frame, but won't always give a % unless the change in baseline is less than the change in time frame (which is not what you want anyways).

You won't be able to develop an estimate % without another variable. Preferably something to reference the absolute total of what you're analyzing to develop that %. Such as =(Baseline/Time)/Total Tasks.

The only reason I recommend the average for an estimate % is because it is only an estimate. It will be dynamic as well since the average will constantly be changing depending on the work completed.

OR you can use the Keep It Simple method and have someone with a lot of experience assigning tasks give you an estimate from the beginning. 😁

Som

We can use below formula,

=ROUND(IF(TODAY() < [Baseline Finish]@row, 0, IF(TODAY() >= [Baseline Finish]@row, 1, NETWORKDAYS([Baseline Finish]@row, TODAY()) / NETWORKDAYS([Baseline Finish]@row, [Baseline Finish]@row))), 2)