RAG Ball Status Formula

Caitlyn

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!

Capture 2.PNG

Capture.PNG

Mark Safran

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

Mark Safran

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

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

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

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.