formula Help

[John5835]

I need a formula to calculate employee vacation time from number of days
worked.
ex. after one year each employee receives 40 hour or 5 days of vacation
time, after two years they receive 10, and after three years on they have
earned 15 days of vacation time.

I cannot get my choose formula, =Choose(a1,10),5,10,15 to work

 

[Mike H]

John,

I wouldn't use Choose because it will fail for someone with anything over 3
yrs service. Try this instead.

=IF(A1<=1,5,IF(A1<=2,10,15))

Mike

 

[Rick Rothstein \(MVP - VB\)]

The index to the Choose function needs to be the counting numbers
(1,2,3,etc.), which you already have in the number of years worked, so try
it this way.

=CHOOSE(A1,5,10,15)

Of course, for the nice regular progression you have, you don't need to use
the CHOOSE function at all... your results are simply 5 times the index
value, so this will do the same thing...

=5*A1

One thing to note, though... I don't presume your earned vacation time
increases forever, so you will need a cap on the calculation. Assuming the
maximum a person can earn is 15 days vacation...

=MAX(15,CHOOSE(A1,5,10,15))

or

=MAX(15,5*A1)

Rick