Special RAG light

[J.Allman]

I want to show variances measured by finish variances; something similar to
this would be fine:
Switch([Finish Variance]/[Minutes Per Day]<=5,1,[Finish
Variance]/[Minutes Per Day]>5 And [Finish Variance]/[Minutes Per
Day]<=10,2,[Finish Variance]/[Minutes Per Day]>10,3)

However I want to highlight the difference between lets say a 30 day task
(what I mean is 30 days worth of [work]) being lets say having a 5 day finish
variance
and
a 1 day task having a 5 day variance.

The thought behind this is that there is a big difference between a 1 day
task being 5 days behind and a 30 day task being 5 days behind.. I can remedy
a 1 day task that is 5 days behind in 1 day whereas I would need 5 full days
of work just to catch up a 30 day task that has a 5 day variance.
Does this logic make sense? Can you show me what I am lacking in this logic?
If it does make sense can someone amend my formula above to produce a
different signal other than the green, amber, or red I already have to
reflect large tasks that are 5 days behind?
Thanks

[Jonathan Sofer [MVP]]

What you would want to do is to divide the finish variance by the over all
task duration or the baseline task duration if you track baselines. This
will give you a percenage instead of a duration and will show you that your
1 day task is 500% late while your 30 day task would only be 17% late.

Jonathan Sofer

"J.Allman" <JAl...@discussions.microsoft.com> wrote in message
news:F4D3F79F-3EAC-4683...@microsoft.com...

[Jim Aksel]

I like that idea. I believe what you will need to do is bury your formula
within a bigger "if" statement. I see where your formula returns 1 for <5, 2
for 5-10, else 3.

That should work by itself. Let's assume you are using a spare number or
text field for your RAG indicator. So, if your calculation returns 2 or 3,
then let's have the new formula return "4" but only if [Duration]/[Minutes
Per Day]>30.

This will get you started:

if (([Your Formula]>1) AND ([Duration]/[Minutes Per Day)>30,4,[Your Formula])

This returns "4" if your formula returns 2 or 3 and the duration of the task
is greater than 30 days. Otherwise, it will return the value you calculate.

Now, in the graphical indicators, you need to specify some other odd color
for the graphic when the value is 4.

If you run into the 255 character limitation for your formula, consider
something like this (assume you use Text1). Add a flag field like Flag1.
Make the formula for Flag 1 this:

if([Text1]>1 AND [Duration]/[Mintues per Day]>30, "Yes","No")
Now you get your standard score in Text1 and an additional Flag1 = "Yes"
when the duration threshold is exceeded as well.
In the case of a Flag field, I usually do a graphical indicator for "Yes"
and no indicator when Flag=No. It keeps it cleaner.

There is a little more information about RAG Lights on my blog, see if any
of that is helpful as well.

If this post was helpful, please consider rating it

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

[J.Allman]

Walk me down this path just a bit farther as I am trying to appreciate the
differences between building the formula off of [work] vs. [duration]. If I
was to build this formula it would be off duration instead of baseline
duration. My concern is some task scenario that could occur that wouldnt give
me an accurate read on variance via duration. To eliminate this concern my
question is:
[Finish] is auto calulated based on actual start, actual work, work (i
assume if work is increased/decreased during scheudle to a number different
from baseline work), and finish date.
Is there any task scenario that you can think of that would occur where the
[duration] would not make an adjustment to reflect the change in task and
thus the variance formula I am trying to build would not be accurate?

[Jim Aksel]

You could change the task type to Fixed Duration and then man load it with
additional resources. That would cause a discrepancy between [work] and
[duration] variances.

Usually, variances are calculated in dollars. That seems odd, but that is
the way the specifications require it. You can do some research on "Earned
Value" on google to learn more. Actually, Project has very good information
on Earned Value in the help.

Try looking at my blog and look at some of the white papers I have on
"Percent Complete." In these papers I am calculating variances but you can
see where [Finish Variance], [Duration Variance], [Start Variance] would all
fit in.
--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com