Conditonal Format multiple RAG status column

Jazz Jones

New Member
Joined
Mar 13, 2017
Messages
10
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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
2016
Platform
Windows
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"})
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
Here is one way:
Code:
=IF(E1>17%,"Under",IF(E1>=15%,"Developing",IF(E1>=9%,"Good",IF(E1>=6%,"Strong","Top"))))
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,974
Office Version
365
Platform
Windows
=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:
Code:
=LOOKUP(E1,{0.01;0.06;0.09;0.15;0.18},{"top";"strong";"good";"developing";"under"})
 

Watch MrExcel Video

Forum statistics

Threads
1,095,347
Messages
5,443,931
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top