Conditonal Format multiple RAG status column

[Jazz Jones]

I've been asked to add a separate column to a spreadsheet specifically to indicate RAG status. There are multiple options within the RAG alert system, though.

I need column F to show Top, Strong, Good, Developing or Under, based on the values in column E, as follows:

5% or less = Top
6% - 8% = Strong
9% - 14% = Good
15% - 17% = Developing
>17% = Under


Can anyone help, please?

 

[snjpverma]

Try this if your data begins from E1 or else change the reference as per your need
=LOOKUP(E1,{0;6;9;15;18},{"top";"strong";"good";"developing";"under"})

Thanks Joe for highlighting that it's a percentages being evaluated. I just overlooked it.

=LOOKUP(E1,{0;0.06;0.09;0.15;0.18},{"top";"strong";"good";"developing";"under"})

 

[Joe4]

Here is one way:

=IF(E1>17%,"Under",IF(E1>=15%,"Developing",IF(E1>=9%,"Good",IF(E1>=6%,"Strong","Top"))))

 

[Joe4]

=LOOKUP(E1,{0;6;9;15;18},{"top";"strong";"good";"developing";"under"})

I really like this solution. Just note that percents are really decimals, so you may need to alter it slightly to reflect that:

=LOOKUP(E1,{0.01;0.06;0.09;0.15;0.18},{"top";"strong";"good";"developing";"under"})