RAG Calculation to highlight Red, Green and Blue balls

Options

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

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    What about something like this...

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

  • Dan Harris
    Dan Harris ✭✭✭✭✭
    Options

    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
    Mike Wilday ✭✭✭✭✭✭
    Options

    =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
    Dan Harris ✭✭✭✭✭
    Options

    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
    Mike Wilday ✭✭✭✭✭✭
    Options

    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!