Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 96
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Start Dates based on Need-By Date? GB Excel Worksheet Functions 2 February 21st 06 06:11 PM
annual increases in price lists smedegaard Excel Discussion (Misc queries) 1 February 17th 06 12:57 PM
Sum cells based on a row variable and seperate column variable CheeseHeadTransplant Excel Worksheet Functions 10 September 23rd 05 06:59 PM
Rank items, select one start date, have remaining dates follow based on rank rob normerica Excel Discussion (Misc queries) 1 August 15th 05 09:36 PM
working with wage tables based on years worked AnnC Excel Discussion (Misc queries) 3 July 21st 05 09:54 PM


All times are GMT +1. The time now is 08:32 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright ©2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"