L
LSquared
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
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