Formula for overall health using symbols

Jeffrey McMillan

Hi All

I am looking for a formula (or any other way) that would update my Overall Health column based on the status of my 5 status categories.

Symbols I am using are:

Green - Project is running on time scope and budget etc

Yellow - 2 or more of the 5 categories are showing yellow

Red - 2 or more of the 5 categories are showing red (even if more are yellow, red should appear)

Blue - Project Completed

SmartLew

ok- in your first post you said

"Yellow - 2 or more of the 5 categories are showing yellow"

but above you say if 2 or fewer are yellow then green. Should it be less than 2 yellow is green?

See how this one works for you. Play around with the combos see if it works.

=IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Blue") = 5, "Blue", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Red") > 1, "Red", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Yellow") > 1, "Yellow", "Green")))

Not sure how you want to represent different combos such as , 2 x blue, 1 x red, 1 x yellow, 1 x green? Currently this will show Green.

Let me know!

SmartLew

What are the criteria for turning the overall health column Green or Blue?

What indicates project is complete?

What makes overall health green?

Jeffrey McMillan

A blue symbol indicated complete. If all the health statuses are blue then the Overall health should be blue to indicate complete

Overall heath should be green if all status' are green, 2 or fewer of the status are showing yellow.

SmartLew

ok- in your first post you said

"Yellow - 2 or more of the 5 categories are showing yellow"

but above you say if 2 or fewer are yellow then green. Should it be less than 2 yellow is green?

See how this one works for you. Play around with the combos see if it works.

=IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Blue") = 5, "Blue", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Red") > 1, "Red", IF(COUNTIF([stakeholder health]@row:[resourcing health]@row, "Yellow") > 1, "Yellow", "Green")))

Not sure how you want to represent different combos such as , 2 x blue, 1 x red, 1 x yellow, 1 x green? Currently this will show Green.

Let me know!

Jeffrey McMillan

That works thank you!!!

SmartLew

@Jeffrey McMillan Great! Happy to help