Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
I have a five year month-by-month personnel budget with the staff names down
column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
Would you be able to use something like the following...
=IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MONTH($B2),DAY($B2)),3%,IF( C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%))) That will show whether 3%, 2%, 1%, or 0% needs to be applied. Hope that is a start. "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
I hope for the sake of your employees I've got this wrong (A 1% rise after 3
yrs!!) but here goes:- First create a cell (C2) with today date in using the formula =now() Then create a cell (D2) that calculates length of service with the formula:- =(C2-B2)/365.25 Note cell must be formatted as general lastly in E2 the formula for the annual increase. =IF(D23,"1% Rise",IF(D22,"3% Rise",IF(D21,"2% Rise",""))) Mike "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
This is exactly the kind of syntax I was looking for, so thanks very much for
your excellent guidance. Your help was exactly the jumping-off point I needed to puzzle this thing out. However, I'm running into a couple of challenges that I hope you might be able to assist me with. I'm pasting my adaptation of your formula below for you to see. The example from my original post was simplified for clarity's sake, but I'm going to give you the more detailed scenario here so we're on the same page and so you'll understand the alternations I've made to your sampe formula: Actual wage schedule: 1. 2.5% at one year anniversary 2. 2.5% at two year anniversary 3. 3% at years three, four, and five anniversaries 4. 1% every year thereafter My anniversary dates are in column C and the starting wage is in column F. My row of months begins in row G. So: =IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1826),( F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4) +1461), (F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4 )+1096), (F4*1.03), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+731), (F4*1.025), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+365), (F4*1.025),F4))))) If it's not obvious, the values for a given month are based on the pay from the previous month. I was having two problems with your formula: 1. Using the Year count caused my wage increases to fall one month too late (e.g. a start date of 11/01/06 should have produced a 2.5% raise as of 11/01/07. but instead didn't show the increase until 12/01/07.) I solved this by changing to a Day count which is working but which seems like a terrible idea...the leap years had me baffled for a good couple of hours. Perhaps there's a better way to do this and get the same result? 2. Using "" caused a problem because once an anniversary date was reached, every month following showed the same % increase since those months also matched the criteria in the IF statement. I resolved this by using "=" instead of "". This works fine except for those years AFTER the fifth year when I need to continue to show an annual increase of 1% for perpetuity. The only solution I can see is to add IF statements to the forumla for year 5, 6, 7, 8...etc. This doesn't seem like a good way to solve the problem. Any thoughts? Thanks again for your prior assistance. I've been at this all day but it's been worth the learning experience and then some. "William Horton" wrote: Would you be able to use something like the following... =IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MONTH($B2),DAY($B2)),3%,IF( C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%))) That will show whether 3%, 2%, 1%, or 0% needs to be applied. Hope that is a start. "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
I simplied my scenario considerably so the truth isn't quite as ugly as all
that. The reduction to a mere 1% annual increase doesn't actually start until the sixth year. It's the joy of working for a non-profit. Thanks for your help. "Mike H" wrote: I hope for the sake of your employees I've got this wrong (A 1% rise after 3 yrs!!) but here goes:- First create a cell (C2) with today date in using the formula =now() Then create a cell (D2) that calculates length of service with the formula:- =(C2-B2)/365.25 Note cell must be formatted as general lastly in E2 the formula for the annual increase. =IF(D23,"1% Rise",IF(D22,"3% Rise",IF(D21,"2% Rise",""))) Mike "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
And I just realized as I tried to populate this down the rows that the day
count is not only messy but that it *won't* work because the leaps years make the counts different depending on the year someone started. Argh. "William Horton" wrote: Would you be able to use something like the following... =IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MONTH($B2),DAY($B2)),3%,IF( C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%))) That will show whether 3%, 2%, 1%, or 0% needs to be applied. Hope that is a start. "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
Here's another approach that you might like to play about with. Set up
a little table somewhere as follows: 0 0% 1 2.5% 2 5.0% 3 8.0% 4 11% 5 14% You can give this table a name, and as you can see it represents the cumulative percentage increase from the starting salary. I have assumed that in year 2 the increase is 5% on the starting salary, rather than 2.5% on top of 102.5 from the previous year, but if this is the case then you can easily re-work the figures. Then basically you will want the working year from the start date up to present - something like this will give you that: =INT((TODAY()-C$2)/365.25) but instead of TODAY() you might like to use the DATE formula you already have. Then you can have a formula like: =IF(calc_year6,(100+calc_year+14)/100,1+ VLOOKUP(calc_year,table_name,2))*F2/12 where calc_year is the above formula. This will give you the monthly salary increased by the appropriate percentage. Hope this helps. Pete On Apr 24, 12:50 am, spence wrote: This is exactly the kind of syntax I was looking for, so thanks very much for your excellent guidance. Your help was exactly the jumping-off point I needed to puzzle this thing out. However, I'm running into a couple of challenges that I hope you might be able to assist me with. I'm pasting my adaptation of your formula below for you to see. The example from my original post was simplified for clarity's sake, but I'm going to give you the more detailed scenario here so we're on the same page and so you'll understand the alternations I've made to your sampe formula: Actual wage schedule: 1. 2.5% at one year anniversary 2. 2.5% at two year anniversary 3. 3% at years three, four, and five anniversaries 4. 1% every year thereafter My anniversary dates are in column C and the starting wage is in column F. My row of months begins in row G. So: =IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1826),( F4*1.03),IF(G$1=DATE(YEAR*($C4),MONTH($C4),DAY($C4 )+1461), (F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4 )+1096), (F4*1.03), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+731), (F4*1.025), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+365), (F4*1.025),F4))))) If it's not obvious, the values for a given month are based on the pay from the previous month. I was having two problems with your formula: 1. Using the Year count caused my wage increases to fall one month too late (e.g. a start date of 11/01/06 should have produced a 2.5% raise as of 11/01/07. but instead didn't show the increase until 12/01/07.) I solved this by changing to a Day count which is working but which seems like a terrible idea...the leap years had me baffled for a good couple of hours. Perhaps there's a better way to do this and get the same result? 2. Using "" caused a problem because once an anniversary date was reached, every month following showed the same % increase since those months also matched the criteria in the IF statement. I resolved this by using "=" instead of "". This works fine except for those years AFTER the fifth year when I need to continue to show an annual increase of 1% for perpetuity. The only solution I can see is to add IF statements to the forumla for year 5, 6, 7, 8...etc. This doesn't seem like a good way to solve the problem. Any thoughts? Thanks again for your prior assistance. I've been at this all day but it's been worth the learning experience and then some. "William Horton" wrote: Would you be able to use something like the following... =IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MO*NTH($B2),DAY($B2)),3%,IF (C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%)) ) That will show whether 3%, 2%, 1%, or 0% needs to be applied. Hope that is a start. "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
variable annual wage increases based on start date
Pete,
Thanks. I'd like to try your approach and I shall when I rework this thing in a few weeks. For now I'm stuck on a bit of a deadline. I solved my Day counting issue but switching to Month, so the only problem I'm left with is how to get the annual anniversary month after year five to bump t (e 1% without having every month that follows that month also move up 1%. Do you know of syntax I could use in an IF statement (nested in my current formula) that would say something like: IF G$1 ($C4)+5 AND the month in those two cells matches, then multiply by 1%? That would alllow me to get my 1% increase only on the anniversary months after five years rather than on every month after five years. Thanks again, spence "Pete_UK" wrote: Here's another approach that you might like to play about with. Set up a little table somewhere as follows: 0 0% 1 2.5% 2 5.0% 3 8.0% 4 11% 5 14% You can give this table a name, and as you can see it represents the cumulative percentage increase from the starting salary. I have assumed that in year 2 the increase is 5% on the starting salary, rather than 2.5% on top of 102.5 from the previous year, but if this is the case then you can easily re-work the figures. Then basically you will want the working year from the start date up to present - something like this will give you that: =INT((TODAY()-C$2)/365.25) but instead of TODAY() you might like to use the DATE formula you already have. Then you can have a formula like: =IF(calc_year6,(100+calc_year+14)/100,1+ VLOOKUP(calc_year,table_name,2))*F2/12 where calc_year is the above formula. This will give you the monthly salary increased by the appropriate percentage. Hope this helps. Pete On Apr 24, 12:50 am, spence wrote: This is exactly the kind of syntax I was looking for, so thanks very much for your excellent guidance. Your help was exactly the jumping-off point I needed to puzzle this thing out. However, I'm running into a couple of challenges that I hope you might be able to assist me with. I'm pasting my adaptation of your formula below for you to see. The example from my original post was simplified for clarity's sake, but I'm going to give you the more detailed scenario here so we're on the same page and so you'll understand the alternations I've made to your sampe formula: Actual wage schedule: 1. 2.5% at one year anniversary 2. 2.5% at two year anniversary 3. 3% at years three, four, and five anniversaries 4. 1% every year thereafter My anniversary dates are in column C and the starting wage is in column F. My row of months begins in row G. So: =IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+1826),( F4*1.03),IF(G$1=DATE(YEARÂ*($C4),MONTH($C4),DAY($C 4)+1461), (F4*1.03),IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4 )+1096), (F4*1.03), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+731), (F4*1.025), IF(G$1=DATE(YEAR($C4),MONTH($C4),DAY($C4)+365), (F4*1.025),F4))))) If it's not obvious, the values for a given month are based on the pay from the previous month. I was having two problems with your formula: 1. Using the Year count caused my wage increases to fall one month too late (e.g. a start date of 11/01/06 should have produced a 2.5% raise as of 11/01/07. but instead didn't show the increase until 12/01/07.) I solved this by changing to a Day count which is working but which seems like a terrible idea...the leap years had me baffled for a good couple of hours. Perhaps there's a better way to do this and get the same result? 2. Using "" caused a problem because once an anniversary date was reached, every month following showed the same % increase since those months also matched the criteria in the IF statement. I resolved this by using "=" instead of "". This works fine except for those years AFTER the fifth year when I need to continue to show an annual increase of 1% for perpetuity. The only solution I can see is to add IF statements to the forumla for year 5, 6, 7, 8...etc. This doesn't seem like a good way to solve the problem. Any thoughts? Thanks again for your prior assistance. I've been at this all day but it's been worth the learning experience and then some. "William Horton" wrote: Would you be able to use something like the following... =IF(C$1DATE(YEAR($B2)+3,MONTH($B2),DAY($B2)),1%,I F(C$1DATE(YEAR($B2)+2,MOÂ*NTH($B2),DAY($B2)),3%,I F(C$1DATE(YEAR($B2)+1,MONTH($B2),DAY($B2)),2%,0%) )) That will show whether 3%, 2%, 1%, or 0% needs to be applied. Hope that is a start. "spence" wrote: I have a five year month-by-month personnel budget with the staff names down column A and their start dates in columb B. The months and years through 2012 go across row 1. My question is, is there a way to build formulas that will calculate annual wage increases based on start date where the increase is: 1. 2% at year one anniversary 2. 3% at year two anniversary 3. 1% at every anniversary thereafter Thanks.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Start Dates based on Need-By Date? | Excel Worksheet Functions | |||
annual increases in price lists | Excel Discussion (Misc queries) | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
working with wage tables based on years worked | Excel Discussion (Misc queries) |