# RAG Calculation to highlight Red, Green and Blue balls

✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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"))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!