Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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 On Mar 19, 4:44 pm, Arne Hegefors wrote: 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: 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" wrote: 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!- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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(LAR GE(D1:F1,1),LARGE(D1:F1,2))) -- Gary''s Student gsnu200711 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rating
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)))) "Gary''s Student" wrote: 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(LAR GE(D1:F1,1),LARGE(D1:F1,2))) -- Gary''s Student gsnu200711 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select colors instead of numbers for rating risk? | Excel Worksheet Functions | |||
How would I pick random team players using a 1 to 5 rating sys? | Excel Worksheet Functions | |||
rating cells 1-5 colour coded HOW? | New Users to Excel | |||
RAG Rating | Charts and Charting in Excel | |||
Rating 1 to 10 | Excel Discussion (Misc queries) |