Assigning specific value to a number range.

[xceldummie]

Hello!

I need help assigning a specific value to a number range. Our life insurance premiums are calucated by the employee's age. A specific rate used in the calculation is assigned according to age. Below are the rates used per age range:

<30=0.21
30-34=0.22
35-39=0.26
40-44=0.37
45-49=0.54
50-54=0.81
55-59=1.36
60-64=1.50
65-69=2.51
70-74=4.40
75+=16.50

The employee's age is listed in column D, I would like the rate to automatically populate in column P.

Thanks in advance for all your help!!!

[jp16]

Use Vlookup, for example, type 30.......75+ in Column A and the assigned rate at Column B, take note, that the Age must be from Smallest to biggest. Then use Vlookup, for example, =vlookup(C1,A1:B11,2,1). Where C1 is the cell u input the age.

[benishiryo]

hi xceldummie,

attached a worksheet for your reference. it is as per what jp16 has mentioned.

[jp16]

However, u need to insert a the minimum age aso. Since minimun age is 0 and the rate is 0.21, therefore, ur data array should be.

0 0.21
30 0.22
35 0.26
40 0.37
45 0.54
50 0.81
55 1.36
60 1.5
65 2.51
70 4.4
75 16.5