IF Function

  • Thread starter Thread starter WebLord
  • Start date Start date
W

WebLord

I just need a refresher on how the IF function works with 3 variables.
This is for calculating bonuses based on how many years of seniority an
employee has. Example is:

1-5 years $4000 bonus
6-15 years $ 6000 bonus
15+ $10000 bonus

All I can remember is by uising the IF Function, I can type
=IF(E8>=1,B42) where E8 is the reference to the year and B42 is an
absolute reference to $4000, but that is only comparing years greater
or equal to 1. How do I create a way to compare the 3 numbers? I am
kinda remembering a nested IF function, but can't recall how to set it
up, is there a formula table in excel for this type of thing?

Hope this makes sense! Thanks for your time...
 
=IF(A1<=5,I1,IF(A1<=15,I2,IF(A1>15,I3,0)))

Where A1 is the number of years and I1:I3 is the value
(4000,6000,10000).

Regards,
Stev
 
Thanks for the reply and your help!

Just another quick question though...:)

When I input .5 for the years (or anything less than a year), I still
get a bonus of $4000? Is the 0 at the end of the formula meant for
anything other than these variables, value is 0?

Thanks again...
 
I wasn't sure if anything under 1 yr would be 0 so modify the origina
formula slightly.

=IF(A1<1,0,IF(A1<=5,I1,IF(A1<=15,I2,IF(A1>15,I3,0))))

The zero at the end is if none of these conditions are met. Broken ou
it is:

IF(A1<0 then return 0, if not then

IF(A1<=5 then return value in I1, if not then

IF(A1<=15 then return calue in I2, if not then

IF(A1>15 then return the value in I3,

if none of the conditions are met then return zero

HTH

Stev
 
I wasn't sure if anything under 1 yr would be 0 so modify the origina
formula slightly.

=IF(A1<1,0,IF(A1<=5,I1,IF(A1<=15,I2,IF(A1>15,I3,0) )))

The zero at the end is if none of these conditions are met. Broken ou
it is:

IF(A1<0 then return 0, if not then

IF(A1<=5 then return value in I1, if not then

IF(A1<=15 then return calue in I2, if not then

IF(A1>15 then return the value in I3,

if none of the conditions are met then return zero

HTH

Stev
 
Back
Top