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?
 

Excel Facts

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

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,454
Office Version
  1. 365
Platform
  1. 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:
Upvote 0

Joe4

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,540
Office Version
  1. 365
Platform
  1. 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"})
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top