Index match formula required

  • Thread starter Kristina1976 via OfficeKB.com
  • Start date
K

Kristina1976 via OfficeKB.com

So I think what I need is a index match formula but not 100% sure.

Basically what I need is an insurance calculator based on age, sex and
smoking status this is what your premium would be.

One work sheet one are my variables. Colum B I have used validations so
only the specific cl can not change them.


A B
Enter current age 30 - 34

Male or Female Male

Smoker or Non Smoker Non-smoker

Desired amount of coverage 50,000

Units of coverage required 50




Worksheet 2

rates per $1,000 of coverage

Male Female
Age Band Smoker Non Smoker Smoker Non Smoker
Up to 24 0.085 0.043 0.034 0.026
25 - 29 0.085 0.043 0.034 0.026
30 - 34 0.094 0.043 0.051 0.034
35 - 39 0.153 0.060 0.085 0.051
40 - 44 0.281 0.102 0.153 0.085
45 - 49 0.476 0.187 0.247 0.136
50 - 54 0.808 0.349 0.374 0.221
55 - 59 1.080 0.468 0.451 0.272
60 + 1.420 0.663 0.604 0.400
 
K

Kristina1976 via OfficeKB.com

Kristina1976 said:
So I think what I need is a index match formula but not 100% sure.

Basically what I need is an insurance premium calculator. So depending on the individual's age, sex and smoking status, I need to know what their premium will be.

On work sheet #1 are my variables, here in column B I have used validations so
only what has been idenified in the lists are options. These of course will change depending on age sex and smoking status and coverage required.

A B
Enter current age 30 - 34

Male or Female Male

Smoker or Non Smoker Non-smoker

Desired amount of coverage 50,000

Units of coverage required 50

cost of premium ??????? So the answer whould be $2.15
But how do I get the formula to work.

Worksheet 2

rates per $1,000 of coverage
A B C
D E
 
Top