I am trying to create a formula for the following information:


0 < 2, 11.33 per month
2 < 5, 14.66 per month
5 < 10, 16.66 per month
10 < 20, 18 per month
20, 20 per month

This is for the following table:

K L M N O
P
DOH Date Tenure Years Accural Rate Total
5 03/21/03 1/31/08 1777 4 0.00
6 03/21/03 2/29/08 1806 4 0.00
7 03/21/03 3/31/08 1837 5 0.00
8 03/21/03 4/30/08 1867 5 0.00
9 03/21/03 5/31/08 1898 5 0.00
10 03/21/03 6/30/08 1928 5 0.00
11 03/21/03 7/31/08 1959 5 0.00
12 03/21/03 8/31/08 1990 5 0.00
13 03/21/03 9/30/08 2020 5 0.00
14 03/21/03 10/31/08 2051 5 0.00
15 03/21/03 11/30/08 2081 5 0.00
16 03/21/03 12/31/08 2112 5 0.00


I need it to calculate the responce in O

Create a lookup table similar to the one below, in what ever column you wish
to use. The example below uses columns AA and AB:

AA AB
0 11.33
2 14.66
5 16.66
10 18
20 20

Then use a VLOOKUP function to return the value. As an example, to return
the accrual rate for row 5 the VLOOKUP would look like the following:

=VLOOKUP(N1,$AA$1:$AB$5,2)

Where N1 has the value of 4 year, the absolute range of $AA$1:$AB$5 is the
lookup table and 2 is the column in the lookup table that contains the value
you want returned.

Hope this helps...
--
Kevin Backmann


"Mandy" wrote:

I am trying to create a formula for the following information:


0 < 2, 11.33 per month
2 < 5, 14.66 per month
5 < 10, 16.66 per month
10 < 20, 18 per month
20, 20 per month

This is for the following table:

K L M N O
P
DOH Date Tenure Years Accural Rate Total
5 03/21/03 1/31/08 1777 4 0.00
6 03/21/03 2/29/08 1806 4 0.00
7 03/21/03 3/31/08 1837 5 0.00
8 03/21/03 4/30/08 1867 5 0.00
9 03/21/03 5/31/08 1898 5 0.00
10 03/21/03 6/30/08 1928 5 0.00
11 03/21/03 7/31/08 1959 5 0.00
12 03/21/03 8/31/08 1990 5 0.00
13 03/21/03 9/30/08 2020 5 0.00
14 03/21/03 10/31/08 2051 5 0.00
15 03/21/03 11/30/08 2081 5 0.00
16 03/21/03 12/31/08 2112 5 0.00


I need it to calculate the responce in O