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
[TABLE="width: 295"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Age Range[/TD]
[TD]Rate per $100.00 of monthly salary[/TD]
[/TR]
[TR]
[TD]15-24[/TD]
[TD="align: right"]$0.10[/TD]
[/TR]
[TR]
[TD]25-29[/TD]
[TD="align: right"]$0.15[/TD]
[/TR]
[TR]
[TD]30-34[/TD]
[TD="align: right"]$0.25[/TD]
[/TR]
[TR]
[TD]35-39[/TD]
[TD="align: right"]$0.40[/TD]
[/TR]
[TR]
[TD]40-44[/TD]
[TD="align: right"]$0.56[/TD]
[/TR]
[TR]
[TD]45-49[/TD]
[TD="align: right"]$0.96[/TD]
[/TR]
[TR]
[TD]50-54[/TD]
[TD="align: right"]$1.17[/TD]
[/TR]
[TR]
[TD]55-59[/TD]
[TD="align: right"]$1.58[/TD]
[/TR]
[TR]
[TD]60-99[/TD]
[TD="align: right"]$1.43[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Make your table like this:

[TABLE="width: 128"]
<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Age Range[/TD]
[TD="class: xl65, width: 64"]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="class: xl65, align: right"]0.10[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="class: xl65, align: right"]0.15[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"]0.25[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="class: xl65, align: right"]0.40[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="class: xl65, align: right"]0.56[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="class: xl65, align: right"]0.96[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="class: xl65, align: right"]1.17[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD="class: xl65, align: right"]1.58[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="class: xl65, align: right"]1.43[/TD]
[/TR]
</tbody>[/TABLE]

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.