I am trying to create a formula

[Mandy]

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

 

[Anand.V.V.N]

I used this formual in my case, with some modification it may suit your
requirement.

=IF(AND(A3>250,A3<=500),"> 250 AND < 500 miles", IF(A3<=250,"< 250
miles",IF(A3>500,"> 500 miles")))

Anand.V.V.N

 

[Bernie Deitrick]

Create a table with these values, in cells A2 to B6
0 11.33
2 14.66
5 16.66
10 18
20 20

Then use the formula

=VLOOKUP(N2,$A$2:$B$6,2)

and copy down.

HTH,
Bernie
MS Excel MVP