Specifying ranges in calc field

[maryb]

I am attempting to create a calculation field that will look at three other fields to determine the value in the calculation field. The 3 existing fields are:

 

Schedule Health (Either Red, Yellow or Green)

Scope Health (Either Red, Yellow or Green)

Budget Health (0 to 10% = yellow and >10% = Red) Currently the result stored in this field is the percentage and the colors are addressed through conditional formatting.

 

The new field is a calculated overall health field -

 

if any of the other 3 fields are yellow, then overall health is yellow

if any of the other 3 fields are red, then overall health is red

if all 3 fields are green, then the calculated overall health is green

 

Can you please help in guiding me to the best way to address this calculated overall health field? I keep bumping up the need to include a range for the budget health in the calulated field and that is where it falls apart. I am new to all of this and need help. I know when writing a script it is possible to use the range (...) feature but it does not seem to work in the calculation field.

 

Thank you!

[AHunter3]

By "are yellow", "are green", etc, do you mean:

 

• that the field is a text field (or calc field of result type "text") with literal value "yellow" or literal value "green", etc? Such that you could say Case (Schedule Health = "green")? Or

 

• that the field is a container field (or calc field of result type "container") with a swatch of green being what's in the container? If so, under what highly specific circumstances? You CAN'T do Case (Schedule Health = {happens to be a green color swatch})! I guess you could do Case (Schedule Health = g.GlobalGreenColorSwatch) but I'd rather assemble it based on the params that CAUSE it to be equated to that color swatch if it were me; OR

 

•*the field neither contains the word "green" nor a green color swatch but is instead merely FORMATTED either as the field color or the text color to be green. Umm, you can't directly & reliably trap for this. Formatting isn't an aspect of the field value. There's no GetFormatColor (Field) function; the TextColor (Field) function is for applying a color, not for assessing whatever color the thing already/currently has. So instead you have to trap for the circumstances that have caused it to be green. What makes it green?

[maryb]

For the Schedule and Scope Health fields - they are text fields with the text "Green", "Yellow" or "Red". The Budget health is a calculation field that calculates the percentage over or under budget and the result is a number.

[AHunter3]

Schedule Health (Either Red, Yellow or Green)

Scope Health (Either Red, Yellow or Green)

Budget Health (0 to 10% = yellow and >10% = Red) Currently the result stored in this field is the percentage and the colors are addressed through conditional formatting.

 

The new field is a calculated overall health field -

 

if any of the other 3 fields are yellow, then overall health is yellow

if any of the other 3 fields are red, then overall health is red

if all 3 fields are green, then the calculated overall health is green

 

OK then...

 

Case (PatternCount (Schedule Health & "¶" & Scope Health & "¶" & Budget Health, "yellow")>0, "yellow";

PatternCount (Schedule Health & "¶" & Scope Health & "¶" & Budget Health, "red")>0, "red";

Schedule Health = "green" and Scope Health = "green" and Budget Health = "green", "green")

 

but what kind of result would you expect if one of the fields is blank? (or is that not possible?)

 

AND... I assumed from the order that YOU listed them in that yellow would pre-empt red. Therefore if Schedule Health is "yellow" and Scope Health is "red", your overall will be "yellow" because that's the order you listed them in. ("If any of the other three are yellow..."). If you want RED to pre-empt yellow, do the Case eval in the other order.

[maryb]

There could be blanks in the schedule and scope healths but not the budget health and i did not address that. Any advice?

 

Actually, on the calculated health we are really looking for it to report the "worst" health of the other 3 healths with worst health being red, followed by yellow and then green. Please note that the budget health field is a calculation field that corresponds to the following health:

 

>0 but less than 10% then yellow

>10% then Red

 

so, if scope health is green, schedule health is green and budget health result is at 11% then the calculated health is Red.

 

if scope health is green, schedule health is yellow and budget health result is at 2% then the calculated health is Yellow, etc.

 

Thanks for your help and patience -I am not only in question with the best commands to use but also how to state the ranges for the budget health.