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:

- 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 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*

Joel BarazanSir Raphael,

Can you please give me the formula for Calculating the Percent of Variance for Duration.

Raphael SantosHi 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

Daniel Suttonlooking for graphical stoplight of deviance from the baseline

Raphael SantosHI Daniel —

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

Please let me know if that helps.

KNHi Sir,

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 SantosHi 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!

LoriWhen 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?

Raphael SantosHi 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!

JacksonI wanted to ask about this formula but how to manipulate it to not include certain durations in the calculation and only consider the durations deemed relevant in our eyes.

Raphael SantosHi 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!

JacksonSo I guess to try to give you as much info as I can without going over board, is basically what I am looking for is an If statement within an if statement. So for each project we have different people assigned to different tasks and we have due dates and durations which affect the duration variance, etc. We also have client tasks with due dates and durations that affect the duration variance, however we want the client duration to not be encompassed by this formula. I was hoping i could write it like:

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

JacksonHey just wondering if you saw my comment and could provide an answer. Thank you!

Raphael SantosHi 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!

Raphael SantosWell, 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!

JacksonOk so the resulting formula didn’t do the job we thought it would do. The issue is now the task that the “Client” is assigned to is affecting the variance of the other tasks, while the “Client” task is not being tracked at all. This was due to my inexperience with Microsoft Project so I am not sure exactly what everything was and most likely conveyed incorrect information. So what I was wondering was would it be easier to calculate the variance of the task of the “Client”, let it affect other tasks’ variance, and then subtract it out with another formula or would it be easier to get that task associated with the “Client” to not affect other tasks? Thank you for your replies! They have provided a lot of insight and help!

Raphael SantosHi 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!

AmyHThis 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!

Raphael SantosHi 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!

M.JHi Sir,

can you provide how to calculate percentage of Duration Variance?

RobertThis thread is old so you might not be around but when I try your formula I get a syntax error and Project highlights the ‘NA”/. I have Office 2010.

yazDear sir,

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