# Conditonal Format multiple RAG status column

#### Jazz Jones

##### New Member
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### snjpverma

##### Well-known Member
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

Here is one way:
Code:
``=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:
Code:
``=LOOKUP(E1,{0.01;0.06;0.09;0.15;0.18},{"top";"strong";"good";"developing";"under"})``

Replies
1
Views
61
Replies
3
Views
370
Replies
3
Views
408
Replies
10
Views
496
Replies
5
Views
143

1,186,777
Messages
5,959,724
Members
438,443
Latest member
azedin4

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back