Rating

[Arne Hegefors]

Hi! I have three columns: A, B and C. They all contain credit rating
information in the form of a grade (eg. AAA or AA-). I want to check the
ratings in column A, B and C and then get a final score in column D. The
rating in one or more columns can be empty. If there is only one rating in
the columns A, B and C then that rating is the final score. If there are two
different ratings, the lowest one shall be the final score. If there are more
than two ratings then the two ones that gives the highest final score is
chosen. If they are different the lowest of those two shall be chosen. If the
two best ratings are the same then they shall be used as a final score.

The ratings scale works like this (from best to worst): AAA, AA+, AA, AA-,
A+, A, A-, BBB+, BBB, BBB-.

So there are two problems: comparing the ratings since they are not
numerical, and then writing a worksheet function that gives the right final
score rating.

If anyone please can help me with this I would be most grateful! Thanks a lot!

 

[Toppers]

Perhaps a start would be to convert, via a table, your ratings to a numeric
value to make any comparisons easier.
A B
AAA 10
AA+ 9

....
BBB- 1

Use VLOOKUP to get the numeric value.

 

[Arne Hegefors]

Yes that is a giid idea and I am most grateful for your help but how do you
take it from there? Are you doomed to use a long series of if-statements or
is there a shorter way to solve it? Thanks alot for all help!!

"Toppers" skrev:

 

[Pete_UK]

You can use MAX to get the largest value, i.e. something like:

=MAX(vlookup on A, vlookup on B, vlookup on C)

This will give you the highest rating, so you might need to use
LARGE( ... , 2) instead.

Hope this helps.

Pete

 

[Gary''s Student]

First make a lookup table. In K1 thru L10 enter:

AAA 10
AA+ 9
AA 8
AA- 7
A+ 6
A 5
A- 4
BBB+ 3
BBB 2
BBB-. 1

Then convert to a number. In D1 enter:
=IF(A1="","",VLOOKUP(A1,$K$1:$L$10,2,FALSE))
and copy to E1 & ,F1

Finally:

=CHOOSE(COUNT(D1:F1),SUM(D1:F1),MIN(D1:F1),MIN(LARGE(D1:F1,1),LARGE(D1:F1,2)))

 

[Toppers]

Variation (but not so neat!) on Gary's reply:

=IF(COUNTA(D2:F2)=1,SUM(D2:F2),IF(COUNTA(D2:F2)=2,MIN(D2:F2),MIN(LARGE(D2:F2,1),LARGE(D2:F2,2))))