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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 218 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!