If age between # and #, then calculate this way, else ??? 2

[loveyoursite]

I need a formula that will calculate a coverage amount using the employee's annual salary and their age as of 1/1/2007. I have all of the pieces, I just don't know how to put it all together into just one cell. For example:

Cell E3 is the age as of 1/1/2007
Cell G3 is a salary of 35,000

If age is <64 the coverage is G3 (1 x sal)
if age is between 65 and 69 the coverage amount = G3*.065 (sal reduced to 65%)
if age is 70+, the coverage amount = G3*.05 (sal reduced to 50%)

So the results would be:
if age is 42 the coverage amt is 35,000
if age is 65 the coverage amount is 22,750
if age is 71 the coverage amount is 17,500

 

[xlhelp]

I live this everyday....so, what you want is

=IF(D5<65,G5,IF(D5<70,G5*0.65,G5*0.5))

I did all my calcs in row 5. D has the age and G has the current salary.

Me transmitte sursum, Caledoni!

 

[AnotherHiggins]

IF statements work like this:

=If(Condition,ResultIfTrue,ResultIfFalse)

You can tie them together like this:
=If(Condition1,ResultIfTrue,If(Condition2,ResultIfTrue,ResultIfFalse))

So the simple way to calculate your criteria would be:
[COLOR=blue white]=IF(E3<=64,G3,IF(E3<=69,G3*0.065,G3*0.05))[/color]

By the way, multiplying by 0.065 gives you 6.5%, not 65%. Likewise, multiplying by 0.05 gives you 5%, not 50%. So your formula really should be:
[COLOR=blue white]=IF(E3<=64,G3,IF(E3<=69,G3*0.65,G3*0.5))[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlhelp]

Question. Does the coverage amount (I assume employee life) have the next higher 500 or or next higher 1000 option?

Me transmitte sursum, Caledoni!

 

[loveyoursite]

Thank you all so much!

 

[AnotherHiggins]

loveyoursite,

On a side note, I looked at your profile and I see that you've been around for a while now. Please consider taking some time to help out other users with your knowledge. I have found that answering the questions of others has really helped me learn a lot.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jfhewitt]

You may find a VLOOKUP table easier to modify periodically.