Answered by:
Custom variance column
Question

Hi, I've been struggling for a few days attempting to create a custom field that would calculate the variance between Baseline Work and another custom column (this custom column just sums the Actual Work and Remaining Work), in a way that will display a percentage.
([Duration2]/[Baseline Work])*100 & "%"
Any pointers or help would be greatly appreciated.
Answers

John,
I got it, finally. It's not perfect but it serves its purpose:
IIf([Baseline1 Work]=0,"0",[Duration1]/[Baseline1 Work])*100
The formula returns an error if the "%" symbol is included, so I modified the name of the field to indicate the value as a percentage. I also needed to change the field to a number in order to use the variance value calculated here in a flag field.
Thanks for your help!
 Marked as answer by Karen Rolling Tuesday, October 27, 2015 4:37 PM
All replies

Using a duration custom field like duration2 for this is not a good idea.
Instead use a number field like number2.
Best Regards  EPMPARTNER  http://www.epmpartner.dk
 Proposed as answer by John Hansleyton Friday, October 23, 2015 5:30 PM
 Unproposed as answer by Karen Rolling Friday, October 23, 2015 5:31 PM


Karen,
Perhaps I'm missing something but since the Work field is the sum of actual work and remaining work, why do you need a separate custom field?
In that case the formula should be in a custom text field as:
Text1= ([Work][Baseline Work])/[Baseline Work]*100 & "%"
And to simplify even further, since there is already a Work Variance field, the formula could be:
Text1= [Work Variance]/[Baseline Work]*100 & "%"
John
 Proposed as answer by John  Project Tuesday, October 27, 2015 9:13 PM
 Unproposed as answer by John  Project Tuesday, October 27, 2015 9:13 PM

Thanks for your reply John. I was unaware of the existence of the Work Variance field. In this example the Work field isn't being used, so it was a case of 'out of sight, out of mind' on my part :)
I tried the formula using the Work Variance field, but I'm getting #ERROR in the column.

Karen,
Two questions. First, did you customize a text field as I indicated or try to use another field? Second, did you set a baseline on all tasks? If some tasks do not have a baseline set, then the formula will indeed result in an "ERROR". That can be fixed with the following tweak to the formula:
Text1=IIf([Baseline Work]=0,"",[Work Variance]/[Baseline Work]*100 & "%")
John

Hi John,
Yes, the field is a custom text field. All tasks have a value indicated for baseline, although some tasks have '0 hrs' as the value.
When I tried your updated formula, the #ERROR disappeared, and now all fields in this column are blank. Is there anything else that could be corrected to make this formula work?

Karen,
Are you saying that the formula yields a blank on every task line? That will occur if the Baseline Work field is zero for every task, but you indicate that some tasks do have a nonzero baseline value, right? Even if the work and baseline work values are equal, the formula will yield a value of 0%, not a blank, so something else must be amiss. Can you post a screen shot showing the Work, Baseline Work, Work Variance, and the custom text field?
By the way, what version of Project are you using? Is it fully updated with the latest updates (i.e. October 2015)?
Also, I assume you are looking at this in the Gantt Chart view, correct? If not, what are you looking at?
Okay, I see you just signed up for the forum today so you won't be able to post a screen shot yet (i.e until your account if verified and that will take several hours). Don't worry about that, we'll find another way.
John
 Edited by John  Project Friday, October 23, 2015 9:03 PM no screen shot

John,
I got it, finally. It's not perfect but it serves its purpose:
IIf([Baseline1 Work]=0,"0",[Duration1]/[Baseline1 Work])*100
The formula returns an error if the "%" symbol is included, so I modified the name of the field to indicate the value as a percentage. I also needed to change the field to a number in order to use the variance value calculated here in a flag field.
Thanks for your help!
 Marked as answer by Karen Rolling Tuesday, October 27, 2015 4:37 PM

Karen,
Okay, but I don't quite understand why you still have a problem. You can only include string information (i.e. text) in a custom text field so if your formula in in a custom Number field, you will get an error if you try to include the "%" symbol.
What exactly do you mean by needing to use a number field because of the variance value calculated in a flag field?
I set up a sample file (see below) with the Text1 field customized as follows:
Text1=IIf([Baseline1 Work]=0,"0",[Duration1]/[Baseline1 Work])*100 & "%"
Baseline data (e.g. Baseline Work 1) can be set using the Set Baseline function, but the Duration1 field can only be populated with a manual entry, the result of a calculation in another field, or with VBA. In the example below, I manually modified the Duration1 field to give the results shown.
By the way, you never did tell me what version of Project you are using and whether it is fully updated.
John

Hi John,
I switched the variance column to a number field  the results are used in a custom flag column that indicates if the variance exceeds 130%. I wasn't able to achieve this with the variance column set to a text field.
In my example, Duration1 = 'Remaining + Actual' and it's the result of a calculation.
Here's what I've got:
I'm using Project Professional 2013 and my IT guy tells me it's been fully updated.

Karen,
Okay, I'm a little confused. In your first few posts you talked about a variance field and I pointed out that Project already has a builtin work variance field. However, later in the thread you indicated you have a problem with this formula:
IIf([Baseline1 Work]=0,"0",[Duration1]/[Baseline1 Work])*100
My first point of confusion. In your last post, you indicate that your Duration1 field is the sum of Remaining (something) and Actual (something). If I assume that is Remaining Work and Actual Work, then again, Project already has that summed value and it is the Work field. So why do you need the Duration1 field?
My second point of confusion. Where does the custom flag field come into play with the formula that "doesn't quite work"?
Perhaps if you could be a bit more verbose in describing your total end goal, we can help you achieve what you want/need without guesswork and extra formulas in custom fields.
John