Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with formula please
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with formula please
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 "kimkay" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with formula please
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 "drhalter" wrote: 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 "kimkay" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with formula please
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with formula please
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. and copy down. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |