How to prorate raises

[ronco1980]

Using 2003. I need to calculate prorated raises for people hired from Jan 2011 to Aug 2011. People hired before Jan 2011 get 3% which is 100% available. People hired after August 2011 will not get any raise.

I have the hire date in column A, then current salary in B. I set up a VLookup table with hire date in A and raise % in B as follows

<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=186 border=0><TBODY><TR><TD width="53%" height=16>Hire Month

​

</TD><TD width="47%" height=16>Increase

​

</TD></TR><TR><TD width="53%" height=16>12/1/2010

​

</TD><TD width="47%" height=16>3.00%

​

</TD></TR><TR><TD width="53%" height=16>1/1/2011

​

</TD><TD width="47%" height=16>2.75%

​

</TD></TR><TR><TD width="53%" height=16>2/1/2011

​

</TD><TD width="47%" height=16>2.50%

​

</TD></TR><TR><TD width="53%" height=16>3/1/2011

​

</TD><TD width="47%" height=16>2.25%

​

</TD></TR><TR><TD width="53%" height=16>4/1/2011

​

</TD><TD width="47%" height=16>2.00%

​

</TD></TR><TR><TD width="53%" height=16>5/1/2011

​

</TD><TD width="47%" height=16>1.75%

​

</TD></TR><TR><TD width="53%" height=16>6/1/2011

​

</TD><TD width="47%" height=16>1.50%

​

</TD></TR><TR><TD width="53%" height=16>7/1/2011

​

</TD><TD width="47%" height=16>1.25%

​

</TD></TR><TR><TD width="53%" height=16>8/1/2011

​

</TD><TD width="47%" height=16>1.00%

​

</TD></TR><TR><TD width="53%" height=16>9/1/2011

​

</TD><TD width="47%" height=16>0.00%

​

</TD></TR><TR><TD width="53%" height=16>10/1/2011

​

</TD><TD width="47%" height=16>0.00%

​

</TD></TR><TR><TD width="53%" height=16>11/1/2011

​

</TD><TD width="47%" height=16>0.00%

​

</TD></TR><TR><TD width="53%" height=16>12/1/2011

​

</TD><TD width="47%" height=16>0.00%

</TD></TR></TBODY></TABLE>​

I'm not sure to account for all those hired before Jan 2011 that would get the full raise. An If statement sounds right but I haven't figured it out.

Is there an easier way to calculate the new salary instead of first a percent then muliple to get the new salary?

I have looked at similar threads but can't quite seem to get everything to work.

I greatly appreciate any suggestions.

 

[p45cal]

Note that the percentages have been shifted up by one cell and sorted in the opposite direction.

Excel Workbook
	A	B	C	D
1	Hire Month	Increase
2	01/12/2011	0.00%
3	01/11/2011	0.00%
4	01/10/2011	0.00%
5	01/09/2011	1.00%
6	01/08/2011	1.25%
7	01/07/2011	1.50%
8	01/06/2011	1.75%
9	01/05/2011	2.00%
10	01/04/2011	2.25%
11	01/03/2011	2.50%
12	01/02/2011	2.75%
13	01/01/2011	3.00%
14	01/12/2010	3.00%
15
16
17
18
19			post xl 2007	pre xl 2007
20	21/10/2010	100	103	103
21	21/11/2010	100	103	103
22	21/12/2010	100	103	103
23	21/01/2011	100	102.75	102.75
24	21/02/2011	100	102.5	102.5
25	21/03/2011	100	102.25	102.25
26	21/04/2011	100	102	102
27	21/05/2011	100	101.75	101.75
28	21/06/2011	100	101.5	101.5
29	21/07/2011	100	101.25	101.25
30	21/08/2011	100	101	101
31	21/09/2011	100	100	100
32	21/10/2011	100	100	100
33	21/11/2011	100	100	100
34	21/12/2011	100	100	100
35	21/01/2012	100	100	100
36	21/02/2012	100	100	100
37	21/03/2012	100	100	100
38	21/04/2012	100	100	100
39	21/05/2012	100	100	100
40	21/06/2012	100	100	100
41	21/07/2012	100	100	100
42	21/08/2012	100	100	100
Sheet18

 

[ronco1980]

p45cal, thank you for responding. You may have to guide me further. I set up the table as you showed on sheet 2. My data is similar starting in A2 and B2 and copied the second formula as I am in 2003. Which cell references do I need to change? I'm getting zero's in C. Thanks.
A B
<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=142 border=0><TBODY><TR><TD width="53%" height=16>

Hire​

​

​

​

​

</TD><TD width="47%" height=16>

Salary​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

12/12/2010​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

1/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

2/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

3/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

4/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

5/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

6/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

7/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

8/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

9/12/2011​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>

11/1/2010​

​

​

​

​

</TD><TD width="47%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="53%" height=16>
</TD><TD width="47%" height=16>
</TD></TR></TBODY></TABLE>

 

[p45cal]

<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C2</td><td>=(1+INDEX(Sheet2!$B$2:$B$14,IFERROR(MATCH(A2,Sheet2!$A$2:$A$14,-11),1)))*B2</td></tr><tr><td>D2</td><td>=(1+INDEX(Sheet2!$B$2:$B$14,IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$14,-1)),1,MATCH(A2,Sheet2!$A$2:$A$14,-1))))*B2</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ronco1980]

p45cal
Thanks, I think I'm getting closer. It catches the prior to 2011 salaries OK, but none of the 2011 change.

<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=236 border=0><TBODY><TR><TD width="32%" height=16>

Hire​

​

​

​

​

</TD><TD width="28%" height=16>

Salary​

​

​

​

​

</TD><TD width="40%" height=16>

New Salary​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

12/12/2010​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

51500​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

1/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

2/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

3/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

4/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

5/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

6/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

7/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

8/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

9/12/2011​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

50000​

​

​

​

​

</TD></TR><TR><TD width="32%" height=16>

11/1/2010​

​

​

​

​

</TD><TD width="28%" height=16>

50000​

​

​

​

​

</TD><TD width="40%" height=16>

51500​

​

​

​

​

</TD></TR></TBODY></TABLE>

 

[dcardno]

Try:

This pro-rates to the day (to the hour, but really...), based on Jan 1 or prior = 3%, Sept 1 and after = 0%. The samples are just to test a couple of dates as well as the outside cases. I think the prior examples were calculating to the nearest month - of course, that might be what you want...

Book1
	A	B	C	D	E
1		HireRange	Rate
2		01-Jan-11	3%
3		01-Sep-11	0%
4
5		ActualHire	OldSalary	Rate	NewSalary
6	EmployeeName1	01-May-11	20,000	1.48%	20,296
7	EmployeeName2	12-Feb-11	30,000	0.52%	30,156
8	EmployeeName3	12-Nov-11	40,000	0.00%	40,000
9	EmployeeName4	12-Nov-10	50,000	3.00%	51,500
10	etc
Sheet1

 

[p45cal]

I'm in the UK and my screenshots show dates in d/m/y format.
Your first post seemed to be in m/d/y format
You may have to adjust for this.

 

[ronco1980]

p45cal...yep, it was the date. It did look like a UK format but didn't mention. Problem solved.

Appreciate sticking with me.

Thank you

 

[dcardno]

All the more reason to use dd/mmm/yy or mmm/d/yy