Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comprae cells
Hello! I have a long list of banks. For every bank there is a rating. The
rating consits of letters and sometimes a plus or minus sign eg AA-, BBB+, A. On another spreadsheet I have a table that consists of ratings and mean default rates. The table looks like:
What I want to do is to get the mean default rate for every bank based on the rating. The problem is that the ratings for the banks have the plus/minus also. Thus I want to ignore them and just pick the rating based on the letters. Any help very much apreciated! Thanks alot! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comprae cells
Not sure if you need to use code. A formula like this might help:
=VLOOKUP(LEFT(B66,LEN(B66)-1),C1:D10,2,FALSE) This assumes that: the table is in range C1:D10 the plus or minus sign is always only one character long at the end of the string the rating to be tested is located in cell B66 Hope this helps J Arne Hegefors wrote: Hello! I have a long list of banks. For every bank there is a rating. The rating consits of letters and sometimes a plus or minus sign eg AA-, BBB+, A. On another spreadsheet I have a table that consists of ratings and mean default rates. The table looks like:
What I want to do is to get the mean default rate for every bank based on the rating. The problem is that the ratings for the banks have the plus/minus also. Thus I want to ignore them and just pick the rating based on the letters. Any help very much apreciated! Thanks alot! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
compare 2 column cells and return the adjacent columns cells data of the cell | Excel Worksheet Functions | |||
Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
Skip cells with TAB/SHIFT+TAB but allow arrow keys/mouse selection of skipped cells | Excel Programming |