RAG Range Formula?

Lindsay Wilson

Hi

I have a RAG legend which looks like this-:

0-49% = Red

50-79% = Amber

80-100% = Green

Like below

I have a formula on my sheet which calculates a percentage on a monthly basis.

I want the percentage to show in the next cell the relevant RAG e.g. 82% would be green or 55% would be amber.

What is the best functions to use for this? https://help.smartsheet.com/functions

Thanks.

Lindsay

Paul Newcome

Ok. You actually have a few different options.

To reference the table:

=INDEX(RAG:RAG, MATCH(MAX(COLLECT([Low End Column]:[Low End Column], [Low End Column]:[Low End Column], @cell <= [% Complete]@row)), [Low End Column]:[Low End Column], 0))

If you don't NEED the table, you can use a pretty straightforward nested IF:

=IF([% Complete]@row <= .49, "Red", IF([% Complete]@row <= .79, "Yellow", IF([% Complete]@row <= 1, "Green", "Grey")))

Lindsay Wilson

Hi

I have RAG legend which is like this

I want a cell to report the RAG based on the range the % is.

E.g. 55% = Amber

10% = Red

Been looking at the function list and not sure where to start, any recommendations for this?

Thanks.

Lindsay

Paul Newcome

What colors would you want for 50% and 80% since you have them overlapped into two different ones?

Lindsay Wilson

@Paul Newcome well that screen grab was an example of one someone else is in the team uses. But my RAG would be

0-49% = Red

50-79% = Amber

80-100% = Green

Lindsay

Paul Newcome

Ok. Next things to check would be the data types...

When you input the percentages in the main portion of the sheet are you entering a number into a text/number column that is formatted for percentages?

Same question as above except for the two columns used in your matrix?

Lindsay Wilson

Yes the column where the % is, is txt/number and formatted with % and likewise for the matrix for the RAG.

Lindsay

Paul Newcome

Ok. You actually have a few different options.

To reference the table:

=INDEX(RAG:RAG, MATCH(MAX(COLLECT([Low End Column]:[Low End Column], [Low End Column]:[Low End Column], @cell <= [% Complete]@row)), [Low End Column]:[Low End Column], 0))

If you don't NEED the table, you can use a pretty straightforward nested IF:

=IF([% Complete]@row <= .49, "Red", IF([% Complete]@row <= .79, "Yellow", IF([% Complete]@row <= 1, "Green", "Grey")))

Lindsay Wilson

@Paul Newcome Yeah I don't really need to look at the table - it's there as a guidance on what each of the RAGs are.

But the second one worked!

Thank you - this is awesome.

Lindsay

Paul Newcome

Happy to help. 👍️