Selecting Lowest of the 3 Credit Ratings (S&P, Moodys, Fitch)

Hello, I am new here and pretty new in the computing world. I am trying to see if I can pick one of you geniusis's brain on how to pick/select/choose a rating that ranges from AAA to D (lowest) from 3 different credit rating sources. For example:

If bond A has a credit rating of AA (S&P) A3 (Moodys) AA (Fitch) where the highest to lowest are as follows:

Moodys S&P Fitch
Aaa AAA AAA
Aa1 AA+ AA+
Aa2 AA AA
Aa3 AA- AA-
A1 A+ A+
A2 A A
A3 A- A-
Baa1 BBB+ BBB+
Baa2 BBB BBB
Baa3 BBB- BBB-
Ba1 BB+ BB+
Ba2 BB BB
Ba3 BB- BB-
B1 B+ B+
B2 B B
B3 B- B-
Caa1 CCC+ CCC+
Caa2 CCC CCC
Caa3 CCC- CCC-
Ca CC CC
C C C
D D

I attached sample worksheet and hopefully you'll be able to show me what to do. Thank you.

As S&P and Fitch appear to be the same then Id use a VLOOKUP statement to find the S&P equivalent of Moodys value and then look that up in a single table to find the ranked number, as per the attached.

Please note, the two tables have to be in Alpha sorted order for the vlookup to work.

I apologize for not being clear of my intent. Assuming columns A thru C are S&P, Moodys and Fitch and the credit rating are CCC, Baa1 and B respectively. I need to see on the fourth column (D) the lowest of the 3 ratings which would be CCC in this case.

So you have a ranking system ranging from A to D which are in columns R,S,T.

I'm assuming you want to add them up essentially and take the average into column V?

My first thought is that it can't be done by formula as you're wanting to perform a calculation on text values. This would leave a macro which will work as you can assign a numeric value to each of your ratings in the background:

Aaa AAA AAA = 1
Aa1 AA+ AA+ = 2
Aa2 AA AA = 3
Aa3 AA- AA- = 4
A1 A+ A+ = 5 etc

so ((Aaa + Aa2 + A1) = (1 + 3 + 5)) / 3 = 3 and would give an overall rating of (whichever format you choose) Aa2 AA AA in column V.

Unless anyone else has any other ideas, I think you may get a result in the Programming section of the forum.

It should be pretty easy to do for the VBA gurus!

Hello, thank you all for the quick responses to my inquiry. @BuZZarD73, creating an average will work only if that is how we want to show the outcome. But in our case, we prefer to be conservative and just take the lowest possible credit rating without doing an average. Hope that makes sense to you.

Hi boopatthiraja, your suggestion seems to work. I added a vlookup to convert the numeric outcome back to letters (credit rating format). I just have one question on this. There will be times that not all 3 credit ratings will report a rating, the report will either show #NA N/A or NR. In this case, if the credit rating came to either

S&P Mdys Fitch
NR C #NA N/A

The formula should bypass NR or #NA and select the next possilbe lowest rating, in this case C. From your ranking example, the answer should be 21. Please advise. Thank you.

Using the rankings supplied by pjwhitfield and the formula of boopathiraja, I've amended the formula to return the text value from the S&P rating.

Does it matter which particular format of rating code is returned? Or is it ok to just return a corresponding correctly ranked rating?

If you need the lowest rating of the particular group establishment that gave it, then it will mean a whole set of rules to be added to the formula.

Hello BuZZarD73, this is so awesome. But unfortunately, I need to see the lowest rating of the particular group otherwise it will produce questions from people who would be looking at the report. Thank you so much for putting some time on this.