Which function to use???
Posted by Becky on September 28, 2001 8:23 AM
Hi!
I can't figure out which function will work for my spreadsheet.
Here is a sample of my chart:
5 6 7 8 9 10
AAA 87 93.6 100.2 106.8 113.4 120
AA 102 108.6 115.2 121.8 128.4 135
A 190 194 198 202 206 210
BBB 210 214 218 222 226 230
I would like to write a formula to look at the rating of a specific holding (AAA, AA, A, or BBB) and the Average life (5 yrs, 6 yrs, 7 yrs, etc...) and show the number that corresponds to the rating and average life. For example, if it has a AA Rating and an average life of 6 years, the result will be 108.6.
Can anyone help me with this?
Thanks!!
Becky
Posted by John S on September 28, 2001 8:35 AM
if your ratings are in column A and your years are in column B your formular would look like
vlookup(a1,table,(b1+1),false)in column C. don't include your Years Heading in your table range.EXAMPLE:if AAA is in cell A1 and 230 was in cell G4 then your "table" is in A1:G4.
Posted by John S on September 28, 2001 8:36 AM
if your ratings are in column A and your years are in column B your formular would look like
vlookup(a1,table,(b1+1),false)in column C. don't include your Years Heading in your table range.EXAMPLE:if AAA is in cell A1 and 230 was in cell G4 then your "table" is in A1:G4.
Posted by John S on September 28, 2001 8:36 AM
if your ratings are in column A and your years are in column B your formular would look like
vlookup(a1,table,(b1+1),false)in column C. don't include your Years Heading in your table range.EXAMPLE:if AAA is in cell A1 and 230 was in cell G4 then your "table" is in A1:G4.
Posted by Aladin Akyurek on September 28, 2001 8:38 AM
Becky --
I'll assume average life values to be in B1:G1 and holding values in A2:A5 with respect to your sample.
To get what you want use:
=INDIRECT(ADDRESS(MATCH("AA",A2:A5,0)+1,MATCH(5,B1:G1,0)+1))
Aladin
Posted by Becky on September 28, 2001 11:09 AM
Aladin & John - more help please!!!!
I apologize, my message doesn't seem very clear.
In the lookup table, the ratings are going down in cells A2, A3, A4, and A5. the average life years go across the top in cells B1, C1, D1, E1, F1, and G1. (Making the table A1:G5) In another part of the spreadsheet, I have many securities listed in rows with information including rating & average life. I would like the formula to look at the rating and average life for the security, and then go to the table and find the value that corresponds to both of them.
I tried using both of the formulas that you submitted, and neither of them seem to work. If you can give me any further assistance, I would appreciate it!!
Becky
Posted by Barrie Davidson on September 28, 2001 11:24 AM
Re: Aladin & John - more help please!!!!
Becky, assuming you are inputting the rating in cell B10 and the average life years in cell B11, the following formula should do the trick.
=INDEX(A1:G5,MATCH(B10,A2:A5,0)+1,MATCH(B11,B1:G1,0)+1)
Regards,
BarrieBarrie Davidson
Posted by Aladin Akyurek on September 28, 2001 11:38 AM
Re: Aladin & John - more help please!!!!
Becky --
You'll probably manage to fit Barrie's formula to your situation. In case you have any difficulty with that, post 3 rows of the data where securities are listed.
Aladin
