Need help in paying salary increase with specific formula

[ehlfb]

Dear Collegues,

I need your help, I have a list of 1000 employees. I need to pay them an increase of 3% on their salary as per the following criteria:

1. All employee to get 3% increase BUT
2. If the salary is <300 and Year of Services > 3 years the MIN increase should be $15
3. If the salary is <300 and Year of Services < 3 years the MIN increase should be $10
4. All other should get 3%

SALARY	JOIN_DATE
233	3-Oct-99
350	1-Mar-03
400	9-May-99
300	2-Jul-00
174	1-Jun-09
242	21-Nov-00
203	10-Jul-05
500	14-Oct-12
600	5-Apr-06
301	25-Jul-04
700	27-Jun-10
214	5-Apr-06
174	19-Jul-09
190	2-May-06
234	7-Jan-01
200	5-Apr-06
197	11-Dec-05
602	30-Mar-72
573	18-May-75
409	2-Oct-90
492	14-Nov-85
385	26-Oct-76
457	11-Aug-79
300	13-May-01

<tbody>
</tbody>

 

[Firefly2012]

Hi

Perhaps this:

Excel 2010
	A	B	C	D	E
1	SALARY	JOIN_DATE	Salary Increase	Ref Date	31/03/2013
2	233	03-Oct-99	15	Min %	3%
3	350	01-Mar-03	10.5	Min Salary	300
4	400	09-May-99	12	Min Inc > 3 years	15
5	300	02-Jul-00	9	Min Inc < 3 years	10
6	174	01-Jun-09	15
7	242	21-Nov-10	10
8	203	10-Jul-05	15
9	500	14-Oct-12	15
10	600	05-Apr-06	18
11	301	25-Jul-04	9.03
12	700	27-Jun-10	21
13	214	05-Apr-06	15
14	174	19-Jul-09	15
15	190	02-May-06	15
16	234	07-Jan-01	15
Sheet1

Cell Formulas
Range	Formula
C2	=MAX(A2*$E$2,IF(A2<$E$3,IF(($E$1-B2)/365>=3,$E$4,$E$5)))

Note the biggest problem here is making sure of the 3 years' employment rule - because it is an inexact formula used above ((ref date - start date)/365)

 

[ehlfb]

Thanks Firefly, it works perfectly. Thanks again and have a nice weekend.

 

[Firefly2012]

You're welcome