# Weighted Average - With Letters

#### lunicus

##### New Member
I'm trying to do a weighted average of credit quality for fixed income solutions - which are graded using letters.
AAA, AA, A, BBB, BB, B, CCC

So I want to great a weighted average of the various investments - so I can change the percentages and letter ratings - and have a weighted average calculated.
Any help out there??

#### Attachments

• Sample.xlsx
9.5 KB · Views: 6
Hi lunicus!

You can use a helper table, when you assign some values to every letter (i did with equal ranges between letters, but you can change the values in the column if you want), and this values can be used to calculate the equivalent letter using weighted average.

Check the file carefully. Blessings!

#### Attachments

• Sample (1).xlsx
12.6 KB · Views: 13
The attached contains the same approach as @John Jairo V with 2 differences, (a) I've used lambda to create a named formula and (b) I've used a ratings table derived from https://www.researchgate.net/public...gn_Credit_Rating_Model_for_European_Countries

As you enter the formula, you'll gets hints about what goes where:
PortFCqs is supposed to mean your PortFolio Credit qualities
PortFWts means Portfolio Weights
CreditQual means your Credit Quality ratings table.
That formula is in cell H3

On top of that, once you have decided how you want to
change the percentages and letter ratings
you can eliminate the credit ratings table on the sheet by hard-coding it into the lambda named function, which I've done using WeightedQuality2, which means you only have to supply 2 ranges:

which is in cell I3.

When changing the percentages and letter ratings, just remember to finish by sorting that table small to large by the number column.

#### Attachments

• Chandoo51871Sample.xlsx
11.3 KB · Views: 10
You guys are amazing!!! Thank you so much!!