Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Stop Light Formula

52 views
Skip to first unread message

dstoddart@blueyonder

unread,
Dec 16, 2008, 3:52:00 PM12/16/08
to

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

JulieS

unread,
Dec 16, 2008, 6:12:02 PM12/16/08
to
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

<dstoddartblue...@DoNotSpam.com> wrote in message
news:dstoddartblue...@DoNotSpam.com...

Jim Aksel

unread,
Dec 16, 2008, 7:45:00 PM12/16/08
to
:)

dstoddart@blueyonder

unread,
Dec 17, 2008, 2:56:13 PM12/17/08
to
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

unread,
Dec 17, 2008, 3:35:31 PM12/17/08
to

"dstoddart <dstoddartblue...@DoNotSpam.com> wrote in
message news:dstoddartblue...@DoNotSpam.com...
<snip>

> 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

unread,
Dec 19, 2008, 8:19:46 AM12/19/08
to

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

JulieS

unread,
Dec 19, 2008, 8:45:25 AM12/19/08
to
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

" <dstoddartblue...@DoNotSpam.com> wrote in message
news:dstoddartblue...@DoNotSpam.com...
>

CharlesM

unread,
Dec 22, 2008, 6:07:01 PM12/22/08
to
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

unread,
Dec 22, 2008, 6:22:30 PM12/22/08
to
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" <Char...@discussions.microsoft.com> wrote in message
news:786A9AB5-83B8-426F...@microsoft.com...

CharlesM

unread,
Dec 22, 2008, 6:51:01 PM12/22/08
to
Golden! You are the best!

corky0770

unread,
Dec 23, 2008, 7:49:39 AM12/23/08
to

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


--
corky0770
------------------------------------------------------------------------
corky0770's Profile: http://forums.techarena.in/members/corky0770.htm

JulieS

unread,
Dec 23, 2008, 8:34:03 AM12/23/08
to
Glad to have helped out Charles and thanks for the feedback.

Julie


"CharlesM" <Char...@discussions.microsoft.com> wrote in message

news:CD45B92C-5208-4145...@microsoft.com...

JulieS

unread,
Dec 23, 2008, 8:38:01 AM12/23/08
to
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

"corky0770" <corky077...@DoNotSpam.com> wrote in message
news:corky077...@DoNotSpam.com...

texmaster

unread,
Oct 8, 2009, 4:50:22 PM10/8/09
to

Hi Everyone.

I've tried pasting this formula into a number field as described but I
continue to get a syntax error and it highlights the second [Finish
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?


--
texmaster
------------------------------------------------------------------------
texmaster's Profile: http://forums.techarena.in/members/142408.htm

Unknown

unread,
Oct 8, 2009, 6:18:54 PM10/8/09
to
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

0 new messages