RAG Ball Status Formula

Options

Hey,

I have put together a formula for RAG ball on a project Smartsheet.


=IF([Variance2]@row <= -2, "Green", IF([Variance2]@row >= -7, "Red", IF([Variance2]@row <= -6, "Yellow", IF([Variance2]@row = 0, "Blue"))


It is half working at the moment except it won't turn blue when there is a zero and for some reason isn't picking up -6 days as a yellow?


Thanks!


Best Answer

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Caitlyn ,

    Is your "Variance2" column type a 'Text/Number' or a 'Duration'? It looks like it's recognizing the values there as text instead of an integer, so the logical expression in the IF( ) statements aren't resolving the way we want them to. If you don't necessarily need that column to be a 'Duration' (i.e. you're not using any of the dependency/predecessor functionality), then dropping the trailing "d" from each value would allow the above formula to work.


    Alternatively, if that Variance2 column is being used as a dependency and we need to keep the current format, we can use this more brtue-force formula instead to look for a hard match of the text values:


    =IF([Variance2]@row = 0, "Blue", IF(OR([Variance2]@row = "-1d", [Variance2]@row = "-2d"), "Green", IF(OR([Variance2]@row = "-3d", [Variance2]@row = "-4d", [Variance2]@row = "-5d", [Variance2]@row = "-6d"), "Yellow", "Red")))




    -MCS

Answers

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Options

    Hey @Caitlyn , can you clarify what number ranges should be what color? The way I'm interpreting is the following:

    0 = Blue

    -1, -2 = Green

    -3, -4, -5, -6 = Yellow

    -7 and lower = Red


    Assuming that's correct, give this formula a shot:

    =IF([Variance2]@row = 0, "Blue", IF([Variance2]@row >= -2, "Green", IF([Variance2]@row >= -6, "Yellow", IF([Variance2]@row <= -7, "Red"))))
    



    If I assumed the number ranges for each color incorrectly let me know and we can revise accordingly.


    -MCS

  • Caitlyn
    Options

    Hey @Mark Safran ,


    Thanks for getting back to me, your range and the image you have attached is exactly what I am looking for but for some reason on mines the colours are not matching up I don't understand why. I have copied your formula straight into it and the colours are wrong on mine. I have attached photos to show you.


    Thanks,


  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Caitlyn ,

    Is your "Variance2" column type a 'Text/Number' or a 'Duration'? It looks like it's recognizing the values there as text instead of an integer, so the logical expression in the IF( ) statements aren't resolving the way we want them to. If you don't necessarily need that column to be a 'Duration' (i.e. you're not using any of the dependency/predecessor functionality), then dropping the trailing "d" from each value would allow the above formula to work.


    Alternatively, if that Variance2 column is being used as a dependency and we need to keep the current format, we can use this more brtue-force formula instead to look for a hard match of the text values:


    =IF([Variance2]@row = 0, "Blue", IF(OR([Variance2]@row = "-1d", [Variance2]@row = "-2d"), "Green", IF(OR([Variance2]@row = "-3d", [Variance2]@row = "-4d", [Variance2]@row = "-5d", [Variance2]@row = "-6d"), "Yellow", "Red")))




    -MCS

  • Caitlyn
    Options

    Hey @Mark Safran


    Thank you so much you've got it sussed! It is being used as dependencies and I didn't realise the formula would have to be different for this so thanks for that.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!