Have you ever wanted to know the impact of a given variance in your schedule percentagewise? 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 byproduct, 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 11page 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 prerequisites 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:
 Click on Project  Custom Fields.
 Select the Number Type and then select the Number1 field (or the first available field if the Number1 is in use).
 Rename the field to % of Finish Variance.
 Click in the Formula button.
 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)


 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.

 In the Calculation for task and group summary rows, select the Use formula option.



 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 Keyusers) 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
Joel Barazan
Can you please give me the formula for Calculating the Percent of Variance for Duration.
Raphael Santos
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
Daniel Sutton
Raphael Santos
Have you seen this article: https://www.mpug.com/articles/increaseyourscheduleunderstandingwithsmartprojectindicators/
Please let me know if that helps.
KN
Is it possible if the certain tasks in a program are delayed by percentage but it is ahead in terms of days (finish variance)? And can you please explain why?
Raphael Santos
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!
Lori
Raphael Santos
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!
Jackson
Raphael Santos
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!
Jackson
IIf([Resource Names]=(“Client”) Or (“Client 1”),(“Skip”),(IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”),0,[Finish Variance]/[Baseline Duration]*100)))
Jackson
Raphael Santos
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!
Raphael Santos
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!
Jackson
Raphael Santos
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/3correctwaystodogreatschedulingwithmicrosoftproject/
Erik van Hurck: 3 incorrect ways to do scheduling with Microsoft Project –
https://www.mpug.com/articles/3incorrectwaystodoschedulingwithmicrosoftproject/
I hope this helps!
AmyH
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!
Raphael Santos
Thanks for sharing your feedback on this. I am glad you find it useful. I am not sure when the 11page guide was published.
Cheers!
M.J
can you provide how to calculate percentage of Duration Variance?
Robert
yaz
Please advise me, currently im using ms project 2016 and doing project task 16k activities. My problem is when i tracking every month, the finish variance column a (day) is not working and i do not know how to get the day even ahead 2%???.When i check the predecessor and successor have relationship.
FYI, Finish variance column is automatic formula from ms project it self.
thank you