Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and sumproduct combine issue
Dear all,
Whether is possible to combine the result return from vlookup with the sumproduct? Here is my issue, A B Aa1 1000000 Baa1 2000000 Ba1 3000000 Caa1 2500000 ..... ..... There is a array code I will need to reflect the rating score, as below, Aaa 1 Aa1 10 Aa2 20 Aa3 40 A1 70 A2 120 A3 180 Baa1 260 Baa2 360 Baa3 610 Ba1 940 Ba2 1350 Ba3 1780 B1 2220 B2 2720 B3 3490 Caa1 4770 Caa2 6500 Caa3 8070 Ca 10000 C 10000 D 10000 NR 10000 I did the way to calculate the weight average rating on total portfolio is I first add one column to vlookup the rating turn into the rating score, and sumproduct the column B and the rating score and then devide total of column B. The final step is index the weight average credit score to return the total portfolio rating, ie. Ba3. Here is my issue, How I can don't add one more column but still can do the calculation to return my weight average rating in one cell? Can I sumproduct(vlookup(A2:A1000),B2:B1000)/subtotal(109,B2:B1000) to get the weighted average score? and I can go from here to index the rating? I seems impossible to vlookup whole array and return each value to sumproduct another column. And here is my issue. Thank you so much Vincent |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
large combine max fuction issue | Excel Worksheet Functions | |||
Sumproduct #N/A! error issue | Excel Worksheet Functions | |||
Issue with sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT issue | Excel Worksheet Functions | |||
SUMPRODUCT - How to combine | Excel Worksheet Functions |