Stop Light Formula

[dstoddart@blueyonder]

Hello

I am trying to set-up a Stop Light function for the tasks in my project
plan so I can have a Red, Amber, Green status for each task. I want to
use the Finish Variance field to show the status of a task so if it's >5
but less than 10 to show Amber, for a variance of >10 it show red
otherwise it should show green.
I have spent hours this afternoon trying to create a formula within the
custom field function to do this without any luck.

If anyone can help with a suitable formula or advise on how I can
achieve the same thing, I would be very grateful and it would save my
frustrations tomorrow when I have another go !

Many thanks
Dave

 

[JulieS]

Hi Dave,

In a number field (Number1 for example) try the following formula:

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)

For tasks with a finish variance of 5 days or less, this will show
1. For tasks with finish variance greater than 5 days but less than
or equal to 10 days it will show 2,. For tasks with a finish
variance greater than 10 days, it will show 3. Then set the
graphical indicators:
Equals 1 - green
Equals 2 - yellow (sorry no amber)
Equals 3 - red

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Jim Aksel]

 

[dstoddart@blueyonder]

Hi Dave,

In a number field (Number1 for example) try the following formula:

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)

For tasks with a finish variance of 5 days or less, this will show
1. For tasks with finish variance greater than 5 days but less than
or equal to 10 days it will show 2,. For tasks with a finish
variance greater than 10 days, it will show 3. Then set the
graphical indicators:
Equals 1 - green
Equals 2 - yellow (sorry no amber)
Equals 3 - red

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

Hello

I am trying to set-up a Stop Light function for the tasks in my
project
plan so I can have a Red, Amber, Green status for each task. I
want to
use the Finish Variance field to show the status of a task so if
it's >5
but less than 10 to show Amber, for a variance of >10 it show red
otherwise it should show green.
I have spent hours this afternoon trying to create a formula
within the
custom field function to do this without any luck.

If anyone can help with a suitable formula or advise on how I can
achieve the same thing, I would be very grateful and it would save
my
frustrations tomorrow when I have another go !

Many thanks
Dave


--
dstoddart@blueyonder
------------------------------------------------------------------------
dstoddart@blueyonder's Profile:
http://forums.techarena.in/members/dstoddart-blueyonder.htm
View this thread:
http://forums.techarena.in/microsoft-project/1088751.htm

http://forums.techarena.in

Thanks ever so much Julie - that did the trick ! The only possible way
it could be improved is if it could ignore tasks that are already
complete. I tried to amend the formula you kindly provided using "AND
[%Complete],100" but this didn't do the trick. Just struggling a little
to get my head around these formulas. Any ideas ?

Thanks again ;-)

 

[JulieS]

message

Thanks ever so much Julie - that did the trick ! The only possible
way
it could be improved is if it could ignore tasks that are already
complete. I tried to amend the formula you kindly provided using
"AND
[%Complete],100" but this didn't do the trick. Just struggling a
little
to get my head around these formulas. Any ideas ?

Thanks again ;-)

<snip>
Hi Dave,

Glad to know the formula is working for you. To your new question,
just add
[% Complete] = 100, 0 to the beginning of the formula. Your amended
formula would read:

Switch([% Complete]=100,0,[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)

You can just leave the graphical indicators as before. Because 0
(the result if the task is complete) isn't in the list of
indicators, no indicator appears.

Julie

 

[dstoddart@blueyonder]

Julie - thanks ever so much, that works brilliantly - you're a star an
have saved me so much trouble. I don't confess to understand the formul
but it does the trick. Are there any resources I can get my hands o
that tells me more about these formulas and what they do ? I have
couple of Project 2007 books but they don't do the subject justice. I
you know of any books or internet sources that cover this topic I'd b
grateful to know.
Thanks again
Dav

 

[JulieS]

Hi Dave,

Glad to hear the amended formula works for you as well. The
formulas in Project are a mixture of Excel and Access formulas with
a few that are just Project thrown in. There is some vague help in
Project but I usually start with Project -- searching for 'Project
Functions' and then either jump over to Access help or Excel help
for specific details. Fellow MVP, Jack Dahlgren also has some info
on his website(s) about custom fields. Try:

http://zo-d.com/blog/archives/programming/working-with-custom-field-formulas.html

Julie

 

[CharlesM]

I have a similar formula which works great, but it does not calculate for
summary tasks. Does anyone know a way to get that to occur?

 

[JulieS]

Hi Charles,

Make sure the option for "Group and summary row" in the customize
field dialog box is set to "Use formula."

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[CharlesM]

Golden! You are the best!

Hi Charles,

Make sure the option for "Group and summary row" in the customize
field dialog box is set to "Use formula."

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[corky0770]

I've done the same thing for the summary row and in some spots i get a
error. any thoughts

 

[JulieS]

Glad to have helped out Charles and thanks for the feedback.

Julie

 

[JulieS]

Hello corky0770,

Sorry, no. Perhaps if you posted your formula we could assist.
Without the formula you've used and more information we would be
just wasting time guessing.

Please see: http://project.mvps.org/posting.htm for some guidance
on posting, particularly the 3rd and 4th bullet points.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[texmaster]

Hi Everyone.

I've tried pasting this formula into a number field as described but
continue to get a syntax error and it highlights the second [Finis
Variance]


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)


What am I doing wrong

 

[Andrew Lavinsky]

Strange. Works just fine when I paste it into a number field.

Have you tried it in another file? Try copy/pasting again?


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm