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!

Capture 2.PNG

Capture.PNG

Find more posts tagged with

Accepted answers

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

All comments

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 ,

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.