Need help with formula please

[Guest]

I need help with a formula to return a value based on a range of numbers, ie:

Cell C3 will contain the number of years an employee has worked for a
company; cell D3 needs to show the number of leave days that employee is
entitled to based on their years of employment. If the employee has worked
0-2 years, leave is 10 days, 2-6 years equals 15 days, 7-14 years = 20 days,
15-24 years = 25 days, and from 25 years up is a flat 30 days of leave. I've
tried several nested if/then statement variations and can't get it to return
all numbers correctly. My programming skills are rudimentary, but I'm a good
copier if someone can help me!

The ranges would actually need to be in decimals, as in 0 - 1.99, 2.00 -
6.99, 7.00 - 14.99, etc.

Any help would be greatly appreciated! The spreadsheet would look like the
following example. Thanks in advance...

Hire Date Tenure in Years Leave Days
5/21/2007 0.07
5/15/2006 1.10
5/15/2003 4.14

 

[Guest]

I think you ought to be able to accomplish this with nested If statements
such as - in cell D3:

=IF(C3<2,10,IF(C3<7,15,IF(C3<15, 20, IF(C3 < 25, 25, IF(C3>=25,30,"Error")))))

You should never see the "Error" that I wrote in at the end.

Good luck
drhalter

 

[Guest]

Thank you! I was making it too difficult; I was trying to use a range
between 2 and 6.99, etc., and couldn't get anything to work. I've tested
this with a number of different date variables and it should do the trick. I
appreciate the help!

Kim

 

[Rick Rothstein \(MVP - VB\)]

I need help with a formula to return a value based on a range of numbers,

ie:

Cell C3 will contain the number of years an employee has worked for a
company; cell D3 needs to show the number of leave days that employee is
entitled to based on their years of employment. If the employee has
worked
0-2 years, leave is 10 days, 2-6 years equals 15 days, 7-14 years = 20
days,
15-24 years = 25 days, and from 25 years up is a flat 30 days of leave.
I've
tried several nested if/then statement variations and can't get it to
return
all numbers correctly. My programming skills are rudimentary, but I'm a
good
copier if someone can help me!

The ranges would actually need to be in decimals, as in 0 - 1.99, 2.00 -
6.99, 7.00 - 14.99, etc.

Any help would be greatly appreciated! The spreadsheet would look like
the
following example. Thanks in advance...

Hire Date Tenure in Years Leave Days
5/21/2007 0.07
5/15/2006 1.10
5/15/2003 4.14

Put this formula in D3...

=IF(C2<2,10,IF(C2<7,15,IF(C2<15,20,IF(C2<25,25,30))))

and copy down.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Put this formula in D3...

=IF(C2<2,10,IF(C2<7,15,IF(C2<15,20,IF(C2<25,25,30))))

Damn! Change the C2's above to C3's.