Calculating Monthly Premium rates base on age

Attached is sheet that I want to use to calculate monthly premium rates based on applicants age over 1,3,4,5, and 10 year terms (see "Assurant" tab). I have figured out how to calculate for one age group (i.e. 51-60) using the IF statement, but don't know how to add a multi-string to accomidate all the other age groups. Can I do this with IF or is there anouther formula I should be using?

Thanks for your reply Alan. I have tried different combination of brackets and formating but always come up with errors.

Below is an example of the IF statements I'm trying to nest:

=IF(AND(E2>0,E2<=40),A8*D15)
=IF(AND(E2>=41,E2<=50),A8*D16)
=IF(AND(E2>=51,E2<=60),A8*D17)
=IF(AND(E2>=61,E2<=70),A8*D18)
=IF(AND(E2>=71,E2<=75),A8*D19)
=IF(AND(E2>=76,E2<=80),A8*D20)
=IF(AND(E2>=81,E2<=85),A8*D21)
=IF(AND(E2>=86,E2<=99),NA

Hi,

I have a solution that I was able to put into your spreadsheet.

It is a little crude but it will work. What I did was chain multiple IF statements toghether in order to find the correct column and row to preform the calculations. It looks something like this.

 =IF(AND($E$2>0,$E$2<=40),C15*$A$8,IF(AND($E$2>40,$E$2<=50),C16*$A$8,""))))

The AND function allows you to add multiple arguments like if number or "age" in cell E2 is greater than 0 and less than or equal to 40 then select cell C15 and multiply it to cell A8. The same is true for all of the age groups as you will be able to see when you view the workbook.

I hope this helps!


Simeon

Thanks Simeon!

Originally Posted by alansidman

I redid your sheet using a series of Vlookups. Look at it.

I was going to use VLOOKUP at first but for some reason I had a brain fart and forgot to make a refrence table for the age groups. I can be dumb at times...

That's why you are the Guru!
Thanks!


Simeon

Thanks Alan!

Thanks All for your help! The calculation now works!! Cheers, Rick

Thanks Audere!