Stoplight Formula Help

[OCBluejay]

I am attempting to create an over Project Status Stoplight. Can anyone
suggest how I can do the following formula. Basically two or more reds of
five lights should be red. All greens should be green, else Yellow. However
with this formula there is the possibility of 4 yellows and one green
equaling 9.

Help please

IIF([Cost] and [Schedule]=â€No baselineâ€, “No baselineâ€, IIF(([CostStoplight]
+ [ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) >=9, “Overall Status Redâ€, IIF(([CostStoplight] +
[ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) =5, “Overall Status Greenâ€, “Overall Status Yellowâ€)

 

[Jan De Messemaeker]

Hi,

It may be because I'm getting old, but I do not understand the first word of
this.
No wonder nobody has even tried to help you so far.
IMHO you are supposing people know the definition of your fields.
My advice is you try again, from the beginning, explaining every expression
that is not standard Project.
Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[OCBluejay]

This is not my server, Someone asked for my help, so please excuse the
generalized field names.

The need is for a Custom field that looks at five separate stoplight
indicators (red,yellow, green) for Cost, Schedule, Scope, Resources and
Budget (the last three values are selected by the PM). If any two of the five
are red then the value would be red. If all five are green then the value
would be green. I hoped to use the numerical values of the stoplights in
order to calculate the value, due to the number of arguments in the formula.

And Help would be appreciated.

Hi,

It may be because I'm getting old, but I do not understand the first word of
this.
No wonder nobody has even tried to help you so far.
IMHO you are supposing people know the definition of your fields.
My advice is you try again, from the beginning, explaining every expression
that is not standard Project.
Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

I am attempting to create an over Project Status Stoplight. Can anyone
suggest how I can do the following formula. Basically two or more reds of
five lights should be red. All greens should be green, else Yellow.
However
with this formula there is the possibility of 4 yellows and one green
equaling 9.

Help please

IIF([Cost] and [Schedule]="No baseline", "No baseline",
IIF(([CostStoplight]
+ [ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) >=9, "Overall Status Red", IIF(([CostStoplight] +
[ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) =5, "Overall Status Green", "Overall Status Yellow")

 

[John]

This is not my server, Someone asked for my help, so please excuse the
generalized field names.

The need is for a Custom field that looks at five separate stoplight
indicators (red,yellow, green) for Cost, Schedule, Scope, Resources and
Budget (the last three values are selected by the PM). If any two of the five
are red then the value would be red. If all five are green then the value
would be green. I hoped to use the numerical values of the stoplights in
order to calculate the value, due to the number of arguments in the formula.

And Help would be appreciated.

Bluejay,
What does, "not my server" mean?

You said that the last three values are selected by the PM - I assume
the first two are derived by a formula based on some project field(s).
At any rate, you can assign the following numerical values for red and
green - yellow doesn't need a value.
red = 1
green = 0

if two or more "stoplights", (whatever those are in your world), have a
"1" then you can test for a total value of 2 or more to set your custom
field to "red". If the total is zero, then the custom field is "green".
If the total is only "1" then the custom field is "yellow".

John
Project MVP

Hi,

It may be because I'm getting old, but I do not understand the first word
of
this.
No wonder nobody has even tried to help you so far.
IMHO you are supposing people know the definition of your fields.
My advice is you try again, from the beginning, explaining every expression
that is not standard Project.
Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

I am attempting to create an over Project Status Stoplight. Can anyone
suggest how I can do the following formula. Basically two or more reds of
five lights should be red. All greens should be green, else Yellow.
However
with this formula there is the possibility of 4 yellows and one green
equaling 9.

Help please

IIF([Cost] and [Schedule]="No baseline", "No baseline",
IIF(([CostStoplight]
+ [ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) >=9, "Overall Status Red", IIF(([CostStoplight] +
[ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) =5, "Overall Status Green", "Overall Status Yellow")

 

[Jack Dahlgren]

John,

Very good idea, but it would essentially disable the stoplight function of
the other customized fields (they could only have two values and thus could
only be red or green and never yellow.

I'd just use some larger values:

Green = 0
Yellow = 1
Red = 10

Any value greater than 20 would indicate that there are at least two reds in
the source fields.

-Jack

This is not my server, Someone asked for my help, so please excuse the
generalized field names.

The need is for a Custom field that looks at five separate stoplight
indicators (red,yellow, green) for Cost, Schedule, Scope, Resources and
Budget (the last three values are selected by the PM). If any two of the
five
are red then the value would be red. If all five are green then the value
would be green. I hoped to use the numerical values of the stoplights in
order to calculate the value, due to the number of arguments in the
formula.

And Help would be appreciated.

Bluejay,
What does, "not my server" mean?

You said that the last three values are selected by the PM - I assume
the first two are derived by a formula based on some project field(s).
At any rate, you can assign the following numerical values for red and
green - yellow doesn't need a value.
red = 1
green = 0

if two or more "stoplights", (whatever those are in your world), have a
"1" then you can test for a total value of 2 or more to set your custom
field to "red". If the total is zero, then the custom field is "green".
If the total is only "1" then the custom field is "yellow".

John
Project MVP

Hi,

It may be because I'm getting old, but I do not understand the first
word
of
this.
No wonder nobody has even tried to help you so far.
IMHO you are supposing people know the definition of your fields.
My advice is you try again, from the beginning, explaining every
expression
that is not standard Project.
Greetings,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
I am attempting to create an over Project Status Stoplight. Can anyone
suggest how I can do the following formula. Basically two or more
reds of
five lights should be red. All greens should be green, else Yellow.
However
with this formula there is the possibility of 4 yellows and one green
equaling 9.

Help please

IIF([Cost] and [Schedule]="No baseline", "No baseline",
IIF(([CostStoplight]
+ [ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) >=9, "Overall Status Red", IIF(([CostStoplight] +
[ScheduleStoplight] + [ScopeStoplight] + [ResourcesStoplight] +
[BudgetStoplight]) =5, "Overall Status Green", "Overall Status
Yellow")