RAG Calculation to highlight Red, Green and Blue balls

Dan Harris

I cannot work out how to combine the following arguments into one cell and calculate, i have spend hours trying and getting no where, would appreciate any help.

The following will return a red or green to the RAG column and works perfectly : =IF([Baseline Variance]@row <= 0, "Green", IF([Baseline Variance]@row >= 1, "Red"))

I also want to pull the blue symbol into the same cell which in isolation the following formula works: =IF([% Complete]@row = 1, "Blue")

How do i combine the formulas to calculate correctly from one cell?

Pulling may hair out!!!

Thanks

Mike Wilday

What about something like this...

=IF([% Complete]@row = 1, "Blue", IF([Baseline Variance]@row <= 0, "Green", IF([Baseline Variance]@row >= 1, "Red")))

Dan Harris

Yes thanks very helpful.

I want to add another argument to the formula whereby if the 'finish date' is in the past RAG will turn red, any suggestions?

Mike Wilday

=IF([Finished Date]@row < Today(), "Red", IF([% Complete]@row = 1, "Blue", IF([Baseline Variance]@row <= 0, "Green", IF([Baseline Variance]@row >= 1, "Red"))))

Try this adjustment.

Dan Harris

Hi, Thanks for the update, really helpful.

Am finding that in some cases the RAG will not turn blue when 100%, this seems to be when the task is already red from being in the past (but with no baseline date) any ideas?

Thanks

Mike Wilday

We can modify the priority by adjusting which check comes first. The first true statement will always stop the IF statement at that true statement. Try this adjustment which prioritizes the statement about if the %complete =100% then blue.

=IF([% Complete]@row = 1, "Blue", IF([Finished Date]@row < Today(), "Red", IF([Baseline Variance]@row <= 0, "Green", IF([Baseline Variance]@row >= 1, "Red"))))