Pricing Chart

[chippette]

I have a column for age. I have a chart with age banded rates ( <25, 25 to
29, etc.) Each age bracket has a rate associated with it. I am trying to do
a formula that would populate in a seperate column the rate associated with a
particular age.

ex:
m2 has age 34
the rate for 30-34 age bracket is $.08
There are more than 7 age brackets so the IF function will not work.
Age brackets are as follows:
<25 = $.05
25-29 = $.06
30-34 = $.08
35-39 = $.09
40-44 = $.10
45-49=$.15
50-54 = $.23
55-59 = $.43
60-64 = $.66
65-69 = $1.27

69 = $2.06

Any suggestions?

 

[Paul C]

set up your data in a table like this

A B C
0 24 $0.05
25 29 $0.06
30 34 $0.08
35 39 $0.09
40 44 $0.10
45 49 $0.15
50 54 $0.23
55 59 $0.43
60 64 $0.66
65 69 $1.27
70 200 $2.06

and use the V lookup function
=vlookup(e1,a1:c11,3)
this formula will lookup the rate for the age value entered in E1

 

[Gord Dibben]

Try the VLOOKUP function.

Or set up a range of two columns I1:J11

With this data

0 0.05
25 0.06
30 0.08
35 0.09
40 0.1
45 0.15
50 0.23
55 0.43
60 0.66
65 1.27
70 2.06

D1 enter this formula =LOOKUP(C1,$I$1:$I$11,$J$1:$J$11)

Enter age values in Column C

Copy D1 down as far as you have ages in column C


Gord Dibben MS Excel MVP

 

[chippette]

Thanks Paul..
A few questions...
1) What does the 3 represent in the formula?
2) I assume that I can put this table on a second worksheet and reference
that way?