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

[DBCane]

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?

 

[onlyadrafter]

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]

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]

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?