Salary Increases

[balusm]

Hi Everyone

I am looking for the best way to arrive at the overall percentage increase of the salary figure [B2:B5] applicable to people based on their employment start and end date (let's assume all still employed). If a person was employed at a given 6 April [A8:A12] an increase applied.



For example, person 1 would have their starting salary of 50,000 increased by 0%, then by 2%, 3% and 5%.

Many thanks or any suggestions!

 

[balusm]

Anyone?

 

[Saba Sabaratnam]

Hi Balusm,

I recreated your table and added five more columns of salary increase dates at the right end of your table.

Enter the following formula in E2 and then copy it down and across

=IF(AND(E$1>$C2,E$1<$D2),INDEX($B$8:$B$12,MATCH(E$1,$A$8:$A$12,0)),"")

The table is given below


[TABLE="width: 980"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Person[/TD]
[TD]Salary[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="align: right"]6/04/2014[/TD]
[TD="align: right"]6/04/2015[/TD]
[TD="align: right"]6/04/2016[/TD]
[TD="align: right"]6/04/2017[/TD]
[TD="align: right"]6/04/2018[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1/01/2015[/TD]
[TD="align: right"]1/06/2018[/TD]
[TD] [/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]22/10/2014[/TD]
[TD="align: right"]1/06/2018[/TD]
[TD] [/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]7/04/2015[/TD]
[TD="align: right"]1/06/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]45000[/TD]
[TD="align: right"]8/04/2014[/TD]
[TD="align: right"]1/06/2018[/TD]
[TD] [/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]5%[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/04/2018[/TD]
[TD="align: right"]5%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/04/2017[/TD]
[TD="align: right"]3%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/04/2016[/TD]
[TD="align: right"]2%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/04/2015[/TD]
[TD="align: right"]0%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/04/2014[/TD]
[TD="align: right"]6%[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]


[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]