How do I do conditional formatting for letters/bond ratings (e.g. AAA, AA+) to show upgrade or downgrade?

DBCane

New Member
Joined
Oct 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello,

First time here so I hope I am following the guidelines correctly.

I'm trying to build an excel sheet that will highlight the change in financial security ratings when a security is upgraded or downgraded each month. If it is upgraded from the previous month I would like it to be highlighted green, if it's downgraded I would like it highlighted red. I know how to do this easy formula with conditional formatting for numbers but I'm not sure how to do it with letters. I think I need to associate each rating with a number (e.g. below) that way I can use an IF > or IF < argument.

Anyone how I do this or if there is a better way?

1602255272899.png
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

If your table of ratings is in A1:A16 and your first month is in D1 and next month is in E1, then in cell E1 have this conditional format:

=MATCH(E1,$A$1:$A$15,0)>MATCH(D1,$A$1:$A$15,0)

and format as red, then add another conditional format

=MATCH(E1,$A$1:$A$15,0)<MATCH(D1,$A$1:$A$15,0)

and format as green.

You can then copy the format from E1 to the cells required.

Is this of any use?
 

DBCane

New Member
Joined
Oct 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Sorry, I'm confused. Don't I need to associate the security ratings with numbers? I enter this rule in conditional formatting or the cell? I can't seem to get this to work but I don't think I'm understanding it correctly.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

No need to associate ratings, as they are in order, the formula will do this for you.
The table of ratings is in A1:A15. In D1 i have entered aaa, then in E1 I have entered aa. Add the conditional formatting to E1, it should turn Red. You will need to change cell refs D1 and E1, to those you are using.
Then use the format painter to format all other cells required, if you format F1 and enter aaa, it should turn green.

Is this any clearer?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,361
Members
412,320
Latest member
sixnine0312
Top