Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert value based on range
Here is what I'm trying to accomplish...I want a formula that will populate
the rate based on the employee's age... So I want it to compare column C to the range in column A and return the rate from column B to Column D. As far as I can tell, there are too many options to use nested IF statements. Can anyone help? A B C D Age Employee EE Age Rate 0-24 $0.05 33 0.08 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.44 60-64 $0.68 65-69 $1.27 70+ $2.06 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert value based on range
I would set your table up slightly differently so that you have only
the start age in column A, like this: A B C D Age Employee EE Age Rate 0 $0.05 33 0.08 25 $0.06 30 $0.08 35 $0.09 40 $0.10 45 $0.15 50 $0.23 55 $0.44 60 $0.68 65 $1.27 70 $2.06 Then in D2 you can have this formula: =VLOOKUP(C2,A$2:B$12,2) Hope this helps. Pete On Oct 29, 8:40*pm, GL wrote: Here is what I'm trying to accomplish...I want a formula that will populate the rate based on the employee's age... So I want it to compare column C to the range in column A and return the rate from column B to Column D. As far as I can tell, there are too many options to use nested IF statements. *Can anyone help? A * * * * * * * B * * * * * * * C * * * * * * *D Age * * Employee * * * *EE Age *Rate 0-24 * *$0.05 * 33 * * *0.08 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.44 60-64 * $0.68 65-69 * $1.27 70+ * * $2.06 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert value based on range
you'll want VLOOKUP, but get rid of the -24 stuff.
so: 0 25 30 35 40 etc for ages Then =VLOOKUP(C2,A:B,2) will return your rate. Cleanest way to get rid of the -'s is Highlight column A Find -* Replace With and leave the box blank Replace all Then just manually get rid of the + at the end "GL" wrote: Here is what I'm trying to accomplish...I want a formula that will populate the rate based on the employee's age... So I want it to compare column C to the range in column A and return the rate from column B to Column D. As far as I can tell, there are too many options to use nested IF statements. Can anyone help? A B C D Age Employee EE Age Rate 0-24 $0.05 33 0.08 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.44 60-64 $0.68 65-69 $1.27 70+ $2.06 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert value based on range
Split your age range into 2 cells:
0-24 Should be: 0.....24 Where 0 is in one cell and 24 is another cell. With your last boundary ,70+, enter it simply as 70. Then you do a lookup on the *lower boundary* age range of cells. Assume: A1:11 = lower age boundary B1:B11 = upper age boundary C1:C11 = numeric values D1 = 33 =VLOOKUP(D1,A1:C11,3) -- Biff Microsoft Excel MVP "GL" wrote in message ... Here is what I'm trying to accomplish...I want a formula that will populate the rate based on the employee's age... So I want it to compare column C to the range in column A and return the rate from column B to Column D. As far as I can tell, there are too many options to use nested IF statements. Can anyone help? A B C D Age Employee EE Age Rate 0-24 $0.05 33 0.08 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.44 60-64 $0.68 65-69 $1.27 70+ $2.06 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Narrow Range based on Selection in Another Range | Excel Discussion (Misc queries) | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
Macro to insert a formula based on a range | Excel Discussion (Misc queries) | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions |