Calculating the Percent of Variance in Microsoft Project

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)

1. 1. 6. 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.
   2. 7. In the Calculation for task and group summary rows, select the Use formula option.

1. 1. 8. 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:

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:

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 Photosteve101 — CC 2.0