Formula help using age ranges, current salary and premium amounts to calculate totals

[thedanzone]

I am trying to create a formula in excel to give me a monthly premium amount for disability coverage. I have current age in one column and annual salary in a other column. I need to take annual salary divided by 12 months then divide by 100. Monthly salary cannot exceed $6000.00. That part is easy, but how to take that figure and run it through the age table and get a rate?

Example 1 - 37 year old making $60k per year would be a $20 monthly premium.
Example 2 - 52 tear old making $250k per year would be a $70.20 monthly premium



Table

Age Range	Rate per $100.00 of monthly salary
15-24	$0.10
25-29	$0.15
30-34	$0.25
35-39	$0.40
40-44	$0.56
45-49	$0.96
50-54	$1.17
55-59	$1.58
60-99	$1.43

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[steve the fish]

Hi. Make your table like this:

Age Range	Rate
15	0.10
25	0.15
30	0.25
35	0.40
40	0.56
45	0.96
50	1.17
55	1.58
60	1.43

<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

and place it in A1:B10. Place your age in D1 and salary in D2. The formula then could be:

=MIN(72000,D2)/1200*LOOKUP(D1,A2:A10,B2:B10)

 

[thedanzone]

Thanks.

Is there a to copy this formula to many rows of data. I am used to dragging the cell down, but the table range cells change when I drag it down.

 

[steve the fish]

Thanks.

Is there a to copy this formula to many rows of data. I am used to dragging the cell down, but the table range cells change when I drag it down.

You need to absolute the cells. Dollar signs around the letters of the ranges.