Nested IF statements with <> values

[Ozzie-Bel]

I'm trying to create a formula that will calculate staff entitlements based on years of service.

1. If they have worked less than 5 years they get nothing.
2. If they have worked between 5-7 years they get 50%
3. If they have worked more than 7 yers they get the full 100%.

=+IF(K1<5,0,IF(K1>4.99<7,(G1*0.5),IF(K1>7,(G1))))

Where K1 = years of service &
G1 = entitlement

This works for arguements 1 and 3 but not 2.

Can someone help?
Thanks Ozzie-Bel

 

[sdsurzh]

Hi,

Try this.

=IF(K1<5,"Nothing",IF(K1>7,"100%",50%))

thanks,
SK

 

[exceldevs]

Hi Ozzie-Bel,

You can also try this:

=+IF(K1<5,0,IF(AND(K1>=5,K1<7),(G1*0.5),IF(K1>=7,(G1))))

hsc

 

[Aladin Akyurek]

I'm trying to create a formula that will calculate staff entitlements based on years of service.

1. If they have worked less than 5 years they get nothing.
2. If they have worked between 5-7 years they get 50%
3. If they have worked more than 7 yers they get the full 100%.

=+IF(K1<5,0,IF(K1>4.99<7,(G1*0.5),IF(K1>7,(G1))))

Where K1 = years of service &
G1 = entitlement

This works for arguements 1 and 3 but not 2.

Can someone help?
Thanks Ozzie-Bel

Something like:

=LOOKUP(K1,{0,5,7},{0,0.5,1})*G1