Severance pay calculation and comparison

[Luwak]

Hi all,

I would like to create an overview of severance payment according to the local regulations in Indonesia, where my company is based. There are two lookup tables:

• A table with entitlements in months of salary based on the years of service. There are three entitlements: severance, reward of service pay and separation pay. Next to that, employees are entitled to an additional 15% of the severance and reward of service combined.
• A table with multipliers depending on the reason of termination.

I have made a calculation worksheet where the user can enter the years of service and the reason of termination. I also made a comparison sheet for different reasons of termination. However, the formulas in this sheet seem complex and inefficient. I have the feeling this can be done better, possibly with the use of array formulas. Can someone help me with this? Thanks in advance!

 

[bosco_yip]

Your formulas appeared unable to copy down, and try to use this one-formula solution as in.

1] You need to change "Tables" sheet header B1 to D1 and G1 to I1 as same as "Calculaion" sheet B4, B5 and B7

2] In D4, formula copied down :

=IF(LEFT(B4)="C",0.15*(C$4+C$5),VLOOKUP(C$3,tbl_Employee_Rights_Multipliers,MATCH(B4,tbl_Employee_Rights_Multipliers[#Headers],0),0)*VLOOKUP(C$2,tbl_Years_of_Service,MATCH(B4,tbl_Years_of_Service[#Headers],0),0))

Regards
Bosco

 

[Luwak]

Thanks! I am always cautious with VLOOKUP because people like to add columns to tables which messes them up (sometimes hard to notice if the data returned is comparable). Combining it with MATCH as you did makes it much more solid.

For the 'Comparison' sheet I have the feeling that an array formula would be better, but I don't know where to start.. any idea on that?

 

[bosco_yip]

Thanks! I am always cautious with VLOOKUP because people like to add columns to tables which messes them up (sometimes hard to notice if the data returned is comparable). Combining it with MATCH as you did makes it much more solid.

For the 'Comparison' sheet I have the feeling that an array formula would be better, but I don't know where to start.. any idea on that?

1] In the "Comparison" sheet, I put the formula in the range of C6:AG8

2] In C6, formula copied across and down :

=SUMPRODUCT(INDEX(Tables!$G$2:$I$13,MATCH($B2,Tables!$F$2:$F$13,0),0)*INDEX(Tables!$B$2:$D$42,MATCH(C$1,Tables!$A$2:$A$42,0),0))*1.15

Regards
Bosco