
1. Re: vlookup off calculated field
Mark Smith Jan 7, 2014 10:10 AM (in response to Frank Gary)This is a good one...I think you're going to be limited because there isn't a way to access the other sheet as an object...its more of a database environment. Unless someone else has a better work around, I would create a calculated field off the [Rating_Weighted] column and use a case statement to pick the letter credit rating:
Case [Rating_Weighted]
When 1 Then 'AAA'
When 2 Then 'AA+'
End

2. Re: vlookup off calculated field
Eric Munisteri Jan 7, 2014 12:14 PM (in response to Frank Gary)Mark is correct, but you have to account for fractions. You could adjust the Calculated field for 'Rating_Weighted" to:
sum([BV]*[Rating])/round(sum([BV])) and that would work, but if a value is 11.526, do you really want it to end up a 12 (BB+ to BB)?
Another solution is to use the following calculated field (I called it "Letter Rating"):
IF [Rating_Weighted] >= 0 AND [Rating_Weighted] <= .9 THEN "SP1+"
ELSEIF [Rating_Weighted] >=1 AND [Rating_Weighted] <= 1.9 THEN "AAA"
ELSEIF [Rating_Weighted] >=2 AND [Rating_Weighted] <= 2.9 THEN "AA+"
ELSEIF [Rating_Weighted] >=3 AND [Rating_Weighted] <= 3.9 THEN "AA"
ELSEIF [Rating_Weighted] >=4 AND [Rating_Weighted] <= 4.9 THEN "AA"
ELSEIF [Rating_Weighted] >=5 AND [Rating_Weighted] <= 5.9 THEN "A+"
ELSEIF [Rating_Weighted] >=6 AND [Rating_Weighted] <= 6.9 THEN "A"
ELSEIF [Rating_Weighted] >=7 AND [Rating_Weighted] <= 7.9 THEN "A"
ELSEIF [Rating_Weighted] >=8 AND [Rating_Weighted] <= 8.9 THEN "BBB+"
ELSEIF [Rating_Weighted] >=9 AND [Rating_Weighted] <= 9.9 THEN "BBB"
ELSEIF [Rating_Weighted] >=10 AND [Rating_Weighted] <= 10.9 THEN "BBB"
ELSEIF [Rating_Weighted] >=11 AND [Rating_Weighted] <= 11.9 THEN "BB+"
ELSEIF [Rating_Weighted] >=12 AND [Rating_Weighted] <= 12.9 THEN "BB"
ELSEIF [Rating_Weighted] >=13 AND [Rating_Weighted] <= 13.9 THEN "BB"
ELSEIF [Rating_Weighted] >=14 AND [Rating_Weighted] <= 14.9 THEN "B+"
ELSEIF [Rating_Weighted] >=15 AND [Rating_Weighted] <= 15.9 THEN "B"
ELSEIF [Rating_Weighted] >=16 AND [Rating_Weighted] <= 16.9 THEN "B"
ELSEIF [Rating_Weighted] >=17 AND [Rating_Weighted] <= 17.9 THEN "CCC+"
ELSEIF [Rating_Weighted] >=18 AND [Rating_Weighted] <= 18.9 THEN "CCC"
ELSEIF [Rating_Weighted] >=19 AND [Rating_Weighted] <= 19.9 THEN "CCC"
ELSEIF [Rating_Weighted] >=20 AND [Rating_Weighted] <= 20.9 THEN "CC"
ELSEIF [Rating_Weighted] >=21 AND [Rating_Weighted] <= 21.9 THEN "C"
ELSEIF [Rating_Weighted] >=22 AND [Rating_Weighted] <= 22.9 THEN "D"
ELSE "Unknown"
END
You could take the .9 and make them .9999999 if you wish.
This calculated field may be too complex to run if you are attached to the excel spreadsheet live. If that is the case, you would need to extract the data.
See if the attached is what you are looking for.

Letter_Rating.zip 46.0 KB
