Create average of 3 credit ratings (moody's, S&P, Fitch)

Hello! I want to create an average for a list of bonds based on credit ratings. For example, if a bond has a Moody's rating of Aa1, S&P rating of AA, and Fitch rating of AA+, then the average credit rating is AA+ (average expressed in S&P). I want to create a formula of some sort so I do not have to go through hundreds of rows and do it manually. I'm assuming I will also use a VLookup so I can have the correlations between the 3 ratings (like I have below). Or maybe I can assign a numerical value to each row of ratings in the table below, do the average calculation, then convert back to a letter rating?

Create an average formula for each bond using this example:

Baa1 A- A




M. S&P Fitch
Aaa AAA AAA..................e.x each value in this row here is a 20
Aa1 AA+ AA+..................19
Aa2 AA AA....................18
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 CCC

Yes that definitely helped! I do have another request though. If one of the ratings is missing (if not all 3 ratings are present for a bond), how do would I alter the formula to take into account the blank? I can't have value be 0 because that would bring the average down. If only 2 ratings are shown and 3rd cell is blank, the average should be out of 2 not 3. If 1 rating is shown, that should be the rating.

Example:

Moody's....S&P....Fitch
A3...........A.......(Blank)

If all 3 ratings are present it works. I can delete Moody's but not S&P. I have not accounted for deleting Fitch yet.

=IF(I4="",IF(H4="",ROUND(AVERAGE(VLOOKUP(H4,$A$2:$D$21,4,FALSE),VLOOKUP(J4,$C$2:$D$21,2,FALSE)),0)),IF(H4="",ROUND(AVERAGE(VLOOKUP(I4,$B$2:$D$21,3,FALSE),VLOOKUP(J4,$C$2:$D$21,2,FALSE)),0),ROUND(AVERAGE(VLOOKUP(H4,$A$2:$D$21,4,FALSE),VLOOKUP(I4,$B$2:$D$21,3,FALSE),VLOOKUP(J4,$C$2:$D$21,2,FALSE)),0)))

You had a slight error in the column of the 4th to last VLOOKUP which I fixed (It's a 3 not a 2).

Please Login or Register  to view this content.

I can delete any 1 of the 3 credit ratings and it gives the right average. The last step I'm working on now is deleting any 2 of the 3 credit ratings and still getting the right average.

Yeah this definitely works better, thanks. Pretty neat and useful formula.

=IF(AND(H2="",I2=""),VLOOKUP(J2,$B$2:$D$2,2,FALSE),IF(AND(H2="",J2=""),VLOOKUP(I2,$B$2:$D$2,3,FALSE),
IF(AND(I2="",J2=""),VLOOKUP(H2,$B$2:$D$2,4,FALSE),IF(H2="",ROUND(AVERAGE(VLOOKUP(I2,$B$2:$D$21,3,FALSE),
VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0),IF(I2="",ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0),IF(J2="",ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(I2,$B$2:$D$21,3,FALSE)),0),ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(I2,$B$2:$D$21,3,FALSE),VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0)))))))

There was actually an error with the cell referencing in the Vlookups when testing for 2 blanks. I went ahead and fixed it. The final formula should be:

=IF(AND(H2="",I2=""),VLOOKUP(J2,$C$2:$D$21,2,FALSE),IF(AND(H2="",J2=""),VLOOKUP(I2,$B$2:$D$21,3,FALSE),
IF(AND(I2="",J2=""),VLOOKUP(H2,$A$2:$D$21,4,FALSE),IF(H2="",ROUND(AVERAGE(VLOOKUP(I2,$B$2:$D$21,3,FALSE),
VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0),IF(I2="",ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0),IF(J2="",ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(I2,$B$2:$D$21,3,FALSE)),0),ROUND(AVERAGE(VLOOKUP(H2,$A$2:$D$21,4,FALSE),
VLOOKUP(I2,$B$2:$D$21,3,FALSE),VLOOKUP(J2,$C$2:$D$21,2,FALSE)),0)))))))

LowestCreditRating.xlsx

The lowest regardless of which ratings are present is attached.

I set it so if there is an error, you get NR instead.

=IF(AND(H2="",I2=""),VLOOKUP(J2,$R$2:$S$43,2,FALSE),IF(AND(H2="",J2=""),VLOOKUP(I2,$R$2:$S$43,2,FALSE),
IF(AND(I2="",J2=""),VLOOKUP(H2,$R$2:$S$43,2,FALSE),IF(H2="",MIN(VLOOKUP(I2,$R$2:$S$43,2,FALSE),
VLOOKUP(J2,$R$2:$S$43,2,FALSE)),IF(I2="",MIN(VLOOKUP(H2,$R$2:$S$43,2,FALSE),
VLOOKUP(J2,$R$2:$S$43,2,FALSE)),IF(J2="",MIN(VLOOKUP(H2,$R$2:$S$43,2,FALSE),
VLOOKUP(I2,$R$2:$S$43,2,FALSE)),MIN(VLOOKUP(H2,$R$2:$S$43,2,FALSE),
VLOOKUP(I2,$R$2:$S$43,2,FALSE),VLOOKUP(J2,$R$2:$S$43,2,FALSE))))))))