Calculating the Percent of Variance in Microsoft Project

 

6812497415_092496ff3b_z

Have you ever wanted to know the impact of a given variance in your schedule percentage-wise? For example, let’s say you have a finish variance of five days in a task that was originally planned to be executed in 20 days. If we calculate the variance of the task (5 days) over what was planned (20 days), the answer will be 25 percent.

In this article I show you how to calculate that and present it in Microsoft Project. As a by-product, you’ll also learn how to create simple formulas that help you customize Project to better fit your needs.

Three Tips for Getting Ready

If you’re not familiar with creating custom formulas in Microsoft Project, I recommend that you have a look at this free, awesome 11-page guide from MPUG that shows the required steps when creating your own custom fields and graphical indicators.

When creating my own custom fields, I like to make sure that some pre-requisites are in place to ensure that the field is calculated properly:

  • Make sure that the schedule has been baselined;
  • Make sure that the task is not a milestone;
  • For formulas that are based on cost, make sure that the task has a planned cost; and
  • For formulas that are based on work, make sure that the task has a planned work.

To understand how to set a baseline in your project, read this MPUG article by Sensei’s Kenneth Steiness on baselining best practices.

Calculating the Percent of Variance for the Finish Date

Let’s get started with dates. Once you go through all the steps of building your schedule and saving a baseline in Microsoft Project, do this:

  1. Click on Project | Custom Fields.
  2. Select the Number Type and then select the Number1 field (or the first available field if the Number1 is in use).
  3. Rename the field to % of Finish Variance.
  4. Click in the Formula button.
  5. Now you should be seeing the Formula for % of Finish Variance dialog box. There you will have to type your custom formula. To facilitate this step, copy and paste the syntax below:

IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”),0,[Finish Variance]/[Baseline Duration]*100)

Raphael_Santos_Calculate_percent_of_variance_figure_1
 

 

      1. Click OK. You will see a message from Microsoft Project saying that now this field will be using the calculated expression you inserted. Click OK again.
      1. In the Calculation for task and group summary rows, select the Use formula option.

Raphael_Santos_Calculate_percent_of_variance_figure_2
 

 

 

 

 

 

      1. Click OK to finish the creation of the formula.

Now, let’s have a look at the syntax to understand the formula that you’ve entered. Let’s start with this section:

IIF([Milestone] OR [Baseline Finish]=ProjDateValue(“NA”),0,

First, this expression is checking to see if the task is a milestone. This comparison is important because, in general, when a task is a milestone, its duration is equal to zero, so we don’t divide any value by it. Second, the expression checks to see if the value inside the Baseline Finish field is equal to “NA” (not available). If one of the two statements is true, then no calculation has to be made.

[Finish Variance]/[Baseline Duration]*100)

If the task is not a milestone and has been baselined, the formula calculates what the percent of variance is and then multiplies the result by 100 to convert it to a whole number.

Now, add the custom field to your table:

Raphael_Santos_Calculate_percent_of_variance_figure_3

After setting the baseline for the project, try to make a change in one or more tasks in order to discover how this affects your schedule. In the example below, I’ll change the duration of the task number 6 (Interviews with Project Management Key-users) from 10 to 11 days. The result will look like this:

Raphael_Santos_Calculate_percent_of_variance_figure_4

It’s now easy to understand that the modified task has a percent of variance of 10 percent (1 day of variance over 10 planned days), while the impact in the whole project is 2.27 percent. (We can also see the impact in all the tasks that are related to the modified one.)

Calculating the Percent of Variance for Cost

In order to create a custom field to calculate the percent of variance for cost, you will have to follow the same steps that were taken previously. However, the syntax of the formula will have a slight change, as follows:

IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”) Or [Baseline Cost]=0,0,[Cost Variance]/[Baseline Cost]*100)

Be aware that, as pointed out, now we’re also examining whether the task has a planned cost before making the calculation.

Calculating the Percent of Variance for Work

The percent of variance for work will be very similar to that used for cost. The syntax will look like this:

IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”) Or [Baseline Work]=0,0,[Work Variance]/[Baseline Work]*100)

There you have it — a simple way to find out the percent of variance across time, cost or work in your project using a basic formula.

Image courtesy of Photosteve101CC 2.0

 

Next Webinar

How to Reverse-Engineer a Microsoft Project Schedule

Written by Raphael Santos
Raphael Santos is a PPM enthusiast with expertise in providing Microsoft Project, Project Online, and Project Server solutions. He has worked in several projects to implement PPM tools, including projects located in Latin America (Brazil, Peru, and Argentina), in the United States, and in Africa. He is also a trainer with more than 10 years of experience teaching users how to use Project Management tools in a more productive way. In 2016, Raphael was awarded the MVP title by Microsoft in recognition of his contributions to the Project Management community. Raphael is a PPM Consultant at Sensei Project Solutions, a certified Microsoft partner specializing in project and portfolio management deployments. Sensei offers a complete set of services to help organizations succeed with their Microsoft PPM deployments. Services include full implementation and training as well as pre-configured solutions and report packs. Visit senseiprojectsolutions.com or contact info@senseiprojectsolutions.com for more information.
Share This Post
Have your say!
00
12 Comments
  1. Hi Joel —

    You can use the following expression to calculate the variance for duration:

    IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”) ,0,[Duration Variance]/[Baseline Duration]*100)

    Hope this helps.
    Raphael

  2. looking for graphical stoplight of deviance from the baseline

  3. HI Daniel —

    Have you seen this article: https://www.mpug.com/articles/increase-your-schedule-understanding-with-smart-project-indicators/

    Please let me know if that helps.

  4. Hi KN,

    I think I did not get your question right. In the example I used in this post, there will only be a percent of variance if the task has a finish variance.

    If you can please clarify the scenario you have, that would help on providing you some answer.

    Thank you!

  5. When I copy the above formula, IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”),0,[Finish Variance]/[Baseline Duration]*100). Project returns a syntax error for the (“NA”) – how do I fix this so the system will accept the formula?

  6. Hi Lori –

    Thanks for your comment. Please try to remove the quotes from the NA (“NA”) and type them instead. Sometimes Microsoft Project does not recognize these elements when we copy and paste them.

    Please let me know if that works.

    Thank you!

  7. Hi Jackson –

    Thanks for your comment. Could you please be more specific on what you called “durations deemed relevant in our eyes?”

    I wanted to have a better understanding on this, so I can provide a better response.

    Thanks!

  8. Hi Jackson –

    Yes, I saw and answered your question. It seems that MPUG for some reason did not publish it 🙁

    Well, could you please try the formula below and let me know if it works?

    IIf([Resource Names]=”Client” OR [Resource Names] = “Client 1”,0,IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”),0,[Finish Variance]/[Baseline Duration]*100))

    One point that you need to be aware of is that you should never assign the resources “Client” or “Client1” alongside with each other or with any other resources in the schedule. These resources must be the only resources assigned to tasks for the formula to work as expected.

    I will wait your feedback.

    Thanks!

  9. Well, just wanted to elaborate my answer a bit more. When I say they should not be assigned alongside with each other or with any other resources, I mean in each task individually.

    Of course you can have as many resources as you want assigned to tasks, but when either “Client” or “Client1” are assigned to a task, that particular task must never have any other resource assigned.

    Hope it makes sense.

    Thanks!

  10. Hi Jackson –

    Well, the way a task affects other tasks in the schedule is directly associated to how you create the links between them (predecessors). If you do not want the tasks assigned to the client to affect other tasks in the schedule (at least directly) you will have to organize them to not be included in the critical path.

    Since you pointed out that you are kind of getting started with MS Project, I would recommend you to read some very good articles on best practices for creating projects using the software:

    Erik van Hurck: 3 correct ways to do great scheduling with Microsoft Project –
    https://www.mpug.com/articles/3-correct-ways-to-do-great-scheduling-with-microsoft-project/

    Erik van Hurck: 3 incorrect ways to do scheduling with Microsoft Project –
    https://www.mpug.com/articles/3-incorrect-ways-to-do-scheduling-with-microsoft-project/

    I hope this helps!

  11. This was an extremely helpful article. Thanks so much for sharing this.

    I also visited the MPUG 11 page guide which was excellent. Do you know in which year this was published, as I would like to cite it in an assignment (as well as your article).

    Thanks again!

  12. Hi Amy –

    Thanks for sharing your feedback on this. I am glad you find it useful. I am not sure when the 11-page guide was published.

    Cheers!

Leave a Reply