Displaying Variance in OnePager

I get asked a lot about how to display variance in OnePager.  This post will hopefully get your creative juices flowing about how to meet your specific needs. You might be interested in a project summary chart that looks something like this:

Variance PV

variance columnsIf so, here’s how to get it. As it turns out, Microsoft Project has two canned fields called Finish Variance and Start Variance.  Both calculate the difference in days between the Baseline Finish and Baseline Start dates, and their current actuals.

In this exercise, I’m going to create a formula in a custom Number column in my Project file (Number1) that will allow me to produce a Finish Variance %, measured against the Duration of the task.  To add the formula in MS Project, right click on any column header and select Custom Fields.  Choose an unused Number column and plug in the following formula (per the screenshot below).

Formula for Variance Percent in Project2Prior to creating your new OnePager Project View, you’ll have to update the Template Properties to pull in that new Number column to be use for Conditional Formatting.  To do this, click on the Templates button in your Add-Ins tab. Once the Template Properties form pops, select the Number column in the appropriate dropdown in the Other Columns tab (per the image below). Then click Save and Use.

Template Changes for Variance2

Now click your OnePager button, and create your New Project View using the updated Template.

Next, create conditional formatting rules for color (or another format attribute) based on your Number column that will represent the Finish Variance %.  See screenshot below:

CFS Rules for Variance

The ordering of the rules is important in this case, so we’ve included a separate shot of that, as well.

3-10-2016 1-43-51 PM

At this point your project view should look something like mine does above.

Do you have a better idea on how to accomplish this visually using your data along with OnePager?  We’d love to hear it!  Please comment or send me a note at [email protected].

2 thoughts on “Displaying Variance in OnePager

  1. Hi,
    Point 1 – I think there is one bracket to many in the formula for calculating the Finish Variance %.. I used the following..’IIf([Duration]=0,[Finish Variance]/48,(([Finish Variance]/[Duration])*100))’. Why use the value 48 in this equation?
    Point 2 – I am trying to colour code my Project View Tasks based on ‘Finish Variance’ in days as opposed to % of the duration. I am having difficulties with this.. Can you help?

    • Thanks for your comments! We definitely appreciate the sanity check. Using 48 in the formula was what Project wanted in order to properly display the correct %change based on the variance value, in the case where Duration=0. I’m not sure why this is the case, but there are a variety of peculiarities in MS Project.

      The reason I chose to use a percentage was because using a pure Days value in the Finish Variance to drive my chart would skew the visual, in my mind. For example, you might have a variety of durations on your activities ranging from 10 days to 100 days. If both of those take an extra day to complete, that’s a 10% vs. a 1% miss on the target, which for me was more telling.

      I’d be happy to help you with conditional formatting using Finish Variance in days, but need to understand specifically what your difficulties are…

Leave a Reply

Your email address will not be published. Required fields are marked *