Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation based on numbers of weeks service
Hi
I need a formula to calculate redundancy in on cell. The current methodology is for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation based on numbers of weeks service
Assuming years of service is in A1, use this in B1
=IF(A1<1,0,IF(A1<2,7,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79))))) for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. "WaqB" wrote: Hi I need a formula to calculate redundancy in on cell. The current methodology is for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation based on numbers of weeks service
Hi Sheeloo
Thanks for that it is almost perfect I forgot to put in my question that those employees that have done more than 1 yrs service but less than 2 will get additional weeks pay pro rated based on the 4 weeks pay basis. Can you let me know what I need to include to capture this "Sheeloo" wrote: Assuming years of service is in A1, use this in B1 =IF(A1<1,0,IF(A1<2,7,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79))))) for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. "WaqB" wrote: Hi I need a formula to calculate redundancy in on cell. The current methodology is for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation based on numbers of weeks service
Try
=IF(A1<1,0,IF(A1<2,7+(A1-1)*4,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79))))) So for 1.5 years result will be 7 + (0.5*4) i.e. 7 + 2. Is this what you want ? "WaqB" wrote: Hi Sheeloo Thanks for that it is almost perfect I forgot to put in my question that those employees that have done more than 1 yrs service but less than 2 will get additional weeks pay pro rated based on the 4 weeks pay basis. Can you let me know what I need to include to capture this "Sheeloo" wrote: Assuming years of service is in A1, use this in B1 =IF(A1<1,0,IF(A1<2,7,IF(A1<11,7+(A1-1)*4,IF(A1<16,43+(A1-10)*3,IF(A1<25,59+(A1-16)*2,79))))) for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. "WaqB" wrote: Hi I need a formula to calculate redundancy in on cell. The current methodology is for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation based on numbers of weeks service
Hi,
Here is another alternative: =(D1=1)*7+(D1=2)*MIN(D1-1,9)*4+(D1=11)*MIN(D1-10,6)*3+(D1=17)*MIN(9,D1-16)*2 Note I did not prorate the first year since it is not clear how you want to handle this - should people with .5 years get a prorate of that *7 or nothing? Also, I think you need to adjust the prior suggestion to read =IF(D1<1,0,IF(D1<2,7,IF(D1<11,7+(D1-1)*4,IF(D1<16,43+(D1-10)*3,IF(D1<25,61+(D1-16)*2,79))))) Where the original had 59 I believe the factor is 61. -- If this helps, please click the Yes button Cheers, Shane Devenshire "WaqB" wrote: Hi I need a formula to calculate redundancy in on cell. The current methodology is for year 1 you receive 7 weeks pay Between Year 2 an 10 you receive 4 weeks pay Between year 11 and 16 year 3 weeks pay from yr 17 to 25 2 weeks pay, capped at 25 years service. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Calculation to return number of weeks and days | Excel Worksheet Functions | |||
# of weeks left based upon a date? | Excel Discussion (Misc queries) | |||
return calculation every four weeks | Excel Discussion (Misc queries) | |||
Years of Service Calculation | Excel Worksheet Functions | |||
Weeks of Supply Calculation | Excel Discussion (Misc queries) |