Answered by:
Baseline variance formula
Question

Can anyone explain how this formula calculate Red/Green/Yellow? What fields are used for this calculation? Why my baseline duration is 5 days VS new duration is 16 days and it's still yellow?
Switch([% Complete]=100,"Complete",(([% Complete]<100) And ([Finish]<Date())),"Overdue",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]<=0,"Green",[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red")
Answers

As Jan notes, Duration is always in working time as defined in the calendar in use in the project. The ProjDateDiff function also uses the "Standard" calendar unless you have a task calendar applied. Are you using the Standard calendar as the project calendar?
Julie
 Marked as answer by Mimi Laongpanich Thursday, December 22, 2011 9:49 PM

Thank Mimi. If you are using the Standard calendar, then any holidays (nonworking time) are not counted in either the total Project Duration nor in any formulas using the ProjDateDiff function.
This is a puzzler.
Julie
 Marked as answer by Mimi Laongpanich Wednesday, January 11, 2012 5:42 PM
All replies

More or less the following logic:
1) If % Complete, then show "Complete"
2) If %Complete < 100 and Finish < Today's Date, "Overdue"  which is a questionable process in my place. Finish should never be before today's date on an incomplete task.
3) No Baseline if no baseline has been saved.
4) No Finish variance = green
5) If the Finish Variance is less than 10% of the overall project duration, yellow.
6) Finish variance greater than 10% of the overall project duration, red.
Generally speaking, I think 36 are reasonable, but I would take issues with #2.
Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky Proposed as answer by Julie SheetsModerator Friday, December 9, 2011 9:47 PM

#2 is slightly different from the others in that it highlights a data quality issue, whereas the others all give a RAG status based on the baseline.
I think #2 is a legitimate check, but not in the context of this field. It belongs alongside other data quality checks like:
 tasks with a past start date that are 0% complete
 tasks with a future start date that are more than 0% complete
 tasks with a future end date that are 100% complete

Andrew,
For 5 and 6, can you show me the calculation from the above snapshot? When it comes to real case scenario, I still don't understand why my Baseline duration = 5 days and Current duration is 16 days and Baseline status is still YELLOW. Why it just turns RED when Current duration is 17 days?

Hi Mimi,
What is the overall duration of your project? As Andrew notes, the portion of the formula:
shows yellow if the Finish Variance is less than or equal to 10% of the project duration
Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",
and red if the Finish Variance is greater than 10% of the project duration.
[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red")
I hope this helps.
Julie

Julie,
Project A has 143.28 days duration. Why "Spec drawings" task has finish variance = 18.75 days and it's still yellow? If I follow what you stated, 18.75/143.28= 13%, which more than 10% and it should turn red.
While "FD step B" task has finish variance = 19.38 and it turns red?

Hi Mimi,
Sorry, you are correct there is something going on which is tough to see without seeing the actual project file. Is this a consolidated project file? Normally "Project A" would be the Project Summary task  line zero  not line 1.
Julie

This is not Master Project file. I select not to show project summary task. My Projec starts at ID1. The reason for that is because this schedule will be added to Master file. At Master file, ID0 will not have baseline information, then I can come down to ID1 which is the top level project that has baseline.

Okay, thanks. This is a somewhat unusual set up but if it suits what you need, fine. I'm guessing that the picture below is not from the master project file, it is from the subproject, "Project A"  as a standalone file?
The formula is in the Project A file, yes?



As Jan notes, Duration is always in working time as defined in the calendar in use in the project. The ProjDateDiff function also uses the "Standard" calendar unless you have a task calendar applied. Are you using the Standard calendar as the project calendar?
Julie
 Marked as answer by Mimi Laongpanich Thursday, December 22, 2011 9:49 PM


Thank Mimi. If you are using the Standard calendar, then any holidays (nonworking time) are not counted in either the total Project Duration nor in any formulas using the ProjDateDiff function.
This is a puzzler.
Julie
 Marked as answer by Mimi Laongpanich Wednesday, January 11, 2012 5:42 PM


Can anyone explain how this formula calculate Red/Green/Yellow? What fields are used for this calculation? Why my baseline duration is 5 days VS new duration is 16 days and it's still yellow?
Switch([% Complete]=100,"Complete",(([% Complete]<100) And ([Finish]<Date())),"Overdue",(([Baseline Start]=ProjDateValue("NA")) Or ([Baseline Finish]=ProjDateValue("NA"))),"No BL",[Finish Variance]<=0,"Green",[Finish Variance]<=(ProjDateDiff([Project Start],[Project Finish])*0.1),"Yellow",[Finish Variance]>(ProjDateDiff([Project Start],[Project Finish])*0.1),"Red")
 Edited by Ismet Kocaman Monday, December 26, 2011 12:20 PM