Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing Chart
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? -- God bless. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing Chart
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 -- If this helps, please remember to click yes. "chippette" wrote: 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? -- God bless. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing Chart
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 On Wed, 18 Nov 2009 12:19:03 -0800, chippette wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing Chart
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? -- God bless. "Paul C" wrote: 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 -- If this helps, please remember to click yes. "chippette" wrote: 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? -- God bless. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pricing help | Excel Discussion (Misc queries) | |||
Pricing Calculation Help | Excel Discussion (Misc queries) | |||
pricing | Excel Discussion (Misc queries) | |||
Old to New Pricing | Excel Worksheet Functions | |||
Formula to extract pricing from a chart | Excel Worksheet Functions |