Stop Light on Work Variance

[Jan]

Hello,

I'm just digging into creating formulas and stoplights and was wondering if
anyone could help with define a formula that would display a graphic
indicator based on work variance; i.e. if work variance is 0 then green, if
work variance is 1 to 10% over work then yellow and work variance greater the
10% then red.

Thaks for any assistance.

Jan

 

[Dale Howard [MVP]]

Jan --

Before you create a formula of any kind that involves analyzing variance,
there are two issues you must consider that determine how you write the
formula:

1. Wha type of field do you want to use? Based on your need, you can use
either a Number field or a Text field.
2. What do you want the formula to display if the task has NOT been
baselined?

Here's a technique I like to use in your situation:

1. Select an available task Number field and name it something like Percent
Work Variance.
2. Enter the following formula:

IIf([Baseline Finish]=ProjDateValue("NA"),-16000,IIf([Milestone],0,[Work
Variance]/[Baseline Work]))

3. In the Custom Fields dialog, select the "Use formula" option in the
Calculation for Task and Group Summary Rows section.
4. In the Values to Display section, click the Graphical Indicators button
and enter the following criteria:

Equals -16,000 Clock Face
Is greater than .10 Red stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green stoplight

In the preceding formula, it first tests for the absence of a baseline and
if true, generates a value of -16,000, which would represent a task
1,600,000% UNDER BUDGET. Because I am using a Number field, the formula HAS
to return some number representing the lack of a baseline, so I selected
this value since it would probably never occur in the real world. The
formula then tests to see if the task is a Milestone task, and if so,
returns a value of 0. Then the formula calculates the percentage of work
variance and displays the value if the two previous tests are false (the
task IS baselined and the task is not a Milestone task).

Let me emphasize that this is only one way to approach your problem. I know
that others like to use other techniques, and gladly invite them to share
their ideas with you as well. Hope this helps.

 

[Jan]

Dale,

Thank you i was definetly making harding than it needed to be; as always
your suggestions are spot on.

Jan

Jan --

Before you create a formula of any kind that involves analyzing variance,
there are two issues you must consider that determine how you write the
formula:

1. Wha type of field do you want to use? Based on your need, you can use
either a Number field or a Text field.
2. What do you want the formula to display if the task has NOT been
baselined?

Here's a technique I like to use in your situation:

1. Select an available task Number field and name it something like Percent
Work Variance.
2. Enter the following formula:

IIf([Baseline Finish]=ProjDateValue("NA"),-16000,IIf([Milestone],0,[Work
Variance]/[Baseline Work]))

3. In the Custom Fields dialog, select the "Use formula" option in the
Calculation for Task and Group Summary Rows section.
4. In the Values to Display section, click the Graphical Indicators button
and enter the following criteria:

Equals -16,000 Clock Face
Is greater than .10 Red stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green stoplight

In the preceding formula, it first tests for the absence of a baseline and
if true, generates a value of -16,000, which would represent a task
1,600,000% UNDER BUDGET. Because I am using a Number field, the formula HAS
to return some number representing the lack of a baseline, so I selected
this value since it would probably never occur in the real world. The
formula then tests to see if the task is a Milestone task, and if so,
returns a value of 0. Then the formula calculates the percentage of work
variance and displays the value if the two previous tests are false (the
task IS baselined and the task is not a Milestone task).

Let me emphasize that this is only one way to approach your problem. I know
that others like to use other techniques, and gladly invite them to share
their ideas with you as well. Hope this helps.

Hello,

I'm just digging into creating formulas and stoplights and was wondering
if
anyone could help with define a formula that would display a graphic
indicator based on work variance; i.e. if work variance is 0 then green,
if
work variance is 1 to 10% over work then yellow and work variance greater
the
10% then red.

Thaks for any assistance.

Jan

 

[Dale Howard [MVP]]

Jan --

You are more than welcome for the help, my friend. Thanks for your kind
comments!

Dale,

Thank you i was definetly making harding than it needed to be; as always
your suggestions are spot on.

Jan

Jan --

Before you create a formula of any kind that involves analyzing variance,
there are two issues you must consider that determine how you write the
formula:

1. Wha type of field do you want to use? Based on your need, you can
use
either a Number field or a Text field.
2. What do you want the formula to display if the task has NOT been
baselined?

Here's a technique I like to use in your situation:

1. Select an available task Number field and name it something like
Percent
Work Variance.
2. Enter the following formula:

IIf([Baseline Finish]=ProjDateValue("NA"),-16000,IIf([Milestone],0,[Work
Variance]/[Baseline Work]))

3. In the Custom Fields dialog, select the "Use formula" option in the
Calculation for Task and Group Summary Rows section.
4. In the Values to Display section, click the Graphical Indicators
button
and enter the following criteria:

Equals -16,000 Clock Face
Is greater than .10 Red
stoplight
Is greater than 0 Yellow
stoplight
Is less than or equal to 0 Green
stoplight

In the preceding formula, it first tests for the absence of a baseline
and
if true, generates a value of -16,000, which would represent a task
1,600,000% UNDER BUDGET. Because I am using a Number field, the formula
HAS
to return some number representing the lack of a baseline, so I selected
this value since it would probably never occur in the real world. The
formula then tests to see if the task is a Milestone task, and if so,
returns a value of 0. Then the formula calculates the percentage of work
variance and displays the value if the two previous tests are false (the
task IS baselined and the task is not a Milestone task).

Let me emphasize that this is only one way to approach your problem. I
know
that others like to use other techniques, and gladly invite them to share
their ideas with you as well. Hope this helps.

Hello,

I'm just digging into creating formulas and stoplights and was
wondering
if
anyone could help with define a formula that would display a graphic
indicator based on work variance; i.e. if work variance is 0 then
green,
if
work variance is 1 to 10% over work then yellow and work variance
greater
the
10% then red.

Thaks for any assistance.

Jan