RAG Ball Status Formula
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

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 brtueforce 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

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

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,

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 brtueforce 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

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
Categories
Check out the Formula Handbook template!