Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to pick a value out of a table
On Tab 1 I have a series of tables with loss years in the rows and tax years
in the columns and percentages filling the tables. See sample below. On tab 2 I need a formula that will pick a specific value based on the combination of loss year and tax year. For example, in the table below I need the value for loss year 1997 in tax year 2006. I then want to be able to copy this formula down to pull the values for subsequent loss years. 2003 2004 2005 2006 1997 84.1041 84.8988 86.1793 88.7355 1998 85.8592 84.1467 84.9399 86.2176 1999 85.3739 85.8781 84.1680 84.9605 2000 82.4462 85.7851 86.2767 84.6181 2001 86.7360 82.6633 85.9327 86.4488 2002 87.3498 87.1282 87.6268 83.9442 2003 91.0149 88.1893 87.9825 88.4479 2004 91.6900 89.0684 88.8771 2005 92.2719 89.8274 2006 92.9914 The resulting table should be the values in the 2006 column. These values will then be multiplied by amounts brought in from another spreadsheet. It looks something like this" IRS Table Discounted Loss Unpd Loss Discount Total by Accident Year Reserves Factor Year 1997 - 88.7355% - 1998 38,000 86.2176% 32,763 1999 280,000 84.9605% 237,889 2000 131,000 84.6181% 110,850 2001 412,000 86.4488% 356,169 2002 1,266,000 83.9442% 1,062,734 2003 1,582,000 88.4479% 1,399,246 2004 3,034,000 88.8771% 2,696,531 2005 5,392,000 89.8274% 4,843,493 2006 18,028,000 92.9914% 16,764,490 Totals 30,310,000 27,643,989 I'm trying to make this an automatic process. Each year the tax year changes (cell a1 on Tab 2), is referenced in the individual tables and the loss years follow back 10 years. I don't want to have to hand load these tables every year. This is just one of many. What I'm basically looking for is a lookup type function that can use two cell references one for the loss year and one for the tax year and return the appropriate factor for that combination. Can anyone point me in teh right direction? Thanks much LSquared |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to pick a value out of a table
I put your data in to A1:E11
A1 is blank; B1:E1 has the tax years; A2:A11 has the loss years In H1 I enter the tax year of interest (eg 2003) and in I1 the loss year (eg1993) This formula find the required number =VLOOKUP(I1,A2:E11,MATCH(H1,A1:E1),FALSE) I get 88.7355 for tax year 2006 & loss year 1997 For Tax year 2004, loss year 2002, I get 87.1282 The march locates which column the Vlookup is to use best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "LSquared" wrote in message ... On Tab 1 I have a series of tables with loss years in the rows and tax years in the columns and percentages filling the tables. See sample below. On tab 2 I need a formula that will pick a specific value based on the combination of loss year and tax year. For example, in the table below I need the value for loss year 1997 in tax year 2006. I then want to be able to copy this formula down to pull the values for subsequent loss years. 2003 2004 2005 2006 1997 84.1041 84.8988 86.1793 88.7355 1998 85.8592 84.1467 84.9399 86.2176 1999 85.3739 85.8781 84.1680 84.9605 2000 82.4462 85.7851 86.2767 84.6181 2001 86.7360 82.6633 85.9327 86.4488 2002 87.3498 87.1282 87.6268 83.9442 2003 91.0149 88.1893 87.9825 88.4479 2004 91.6900 89.0684 88.8771 2005 92.2719 89.8274 2006 92.9914 The resulting table should be the values in the 2006 column. These values will then be multiplied by amounts brought in from another spreadsheet. It looks something like this" IRS Table Discounted Loss Unpd Loss Discount Total by Accident Year Reserves Factor Year 1997 - 88.7355% - 1998 38,000 86.2176% 32,763 1999 280,000 84.9605% 237,889 2000 131,000 84.6181% 110,850 2001 412,000 86.4488% 356,169 2002 1,266,000 83.9442% 1,062,734 2003 1,582,000 88.4479% 1,399,246 2004 3,034,000 88.8771% 2,696,531 2005 5,392,000 89.8274% 4,843,493 2006 18,028,000 92.9914% 16,764,490 Totals 30,310,000 27,643,989 I'm trying to make this an automatic process. Each year the tax year changes (cell a1 on Tab 2), is referenced in the individual tables and the loss years follow back 10 years. I don't want to have to hand load these tables every year. This is just one of many. What I'm basically looking for is a lookup type function that can use two cell references one for the loss year and one for the tax year and return the appropriate factor for that combination. Can anyone point me in teh right direction? Thanks much LSquared |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to pick a value out of a table
Thanks for the starting point. The tax and loss years are already set up on
the results tables but I believe I can adapt the formula to reference the correct cells. I really appreciate the help. LSquared "Bernard Liengme" wrote: I put your data in to A1:E11 A1 is blank; B1:E1 has the tax years; A2:A11 has the loss years In H1 I enter the tax year of interest (eg 2003) and in I1 the loss year (eg1993) This formula find the required number =VLOOKUP(I1,A2:E11,MATCH(H1,A1:E1),FALSE) I get 88.7355 for tax year 2006 & loss year 1997 For Tax year 2004, loss year 2002, I get 87.1282 The march locates which column the Vlookup is to use best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "LSquared" wrote in message ... On Tab 1 I have a series of tables with loss years in the rows and tax years in the columns and percentages filling the tables. See sample below. On tab 2 I need a formula that will pick a specific value based on the combination of loss year and tax year. For example, in the table below I need the value for loss year 1997 in tax year 2006. I then want to be able to copy this formula down to pull the values for subsequent loss years. 2003 2004 2005 2006 1997 84.1041 84.8988 86.1793 88.7355 1998 85.8592 84.1467 84.9399 86.2176 1999 85.3739 85.8781 84.1680 84.9605 2000 82.4462 85.7851 86.2767 84.6181 2001 86.7360 82.6633 85.9327 86.4488 2002 87.3498 87.1282 87.6268 83.9442 2003 91.0149 88.1893 87.9825 88.4479 2004 91.6900 89.0684 88.8771 2005 92.2719 89.8274 2006 92.9914 The resulting table should be the values in the 2006 column. These values will then be multiplied by amounts brought in from another spreadsheet. It looks something like this" IRS Table Discounted Loss Unpd Loss Discount Total by Accident Year Reserves Factor Year 1997 - 88.7355% - 1998 38,000 86.2176% 32,763 1999 280,000 84.9605% 237,889 2000 131,000 84.6181% 110,850 2001 412,000 86.4488% 356,169 2002 1,266,000 83.9442% 1,062,734 2003 1,582,000 88.4479% 1,399,246 2004 3,034,000 88.8771% 2,696,531 2005 5,392,000 89.8274% 4,843,493 2006 18,028,000 92.9914% 16,764,490 Totals 30,310,000 27,643,989 I'm trying to make this an automatic process. Each year the tax year changes (cell a1 on Tab 2), is referenced in the individual tables and the loss years follow back 10 years. I don't want to have to hand load these tables every year. This is just one of many. What I'm basically looking for is a lookup type function that can use two cell references one for the loss year and one for the tax year and return the appropriate factor for that combination. Can anyone point me in teh right direction? Thanks much LSquared |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup - can't pick up table array | Excel Worksheet Functions | |||
pick up cells | Excel Discussion (Misc queries) | |||
Table to pick out most common entries and count occurences of each | Excel Worksheet Functions | |||
how to pick from a range of table values | Excel Worksheet Functions | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) |