support

Calculate through Multi-table Join

On the basis of a Standard Wage table, an Absence table, and a Performance table, calculate the net wages of employees using the following formula: Payable Weekly Wage=Standard Weekly Wage*(1+Performance bonus – Absence hours/40)+Bonus.

esCalc_excel_multi_table_1

esCalc_excel_multi_table_2

Since the data is stored in three worksheets, a Join operation according to employee EIDs is needed.

Use LOOKUP formulas to consolidate the data in different worksheets:

esCalc_excel_multi_table_3

Then, calculate the weekly wage payable:

esCalc_excel_multi_table_4

When using Excel to handle multi-table joins, usually you will have to locate and reference necessary data through complicated formulas. That is difficult to operate, to view or modify. If you don’t know how to use formulas to locate and reference data in Excel, then you will have to do it manually. That will be even more complex.

esCalc_excel_multi_table_5

esCalc_excel_multi_table_6

In order to obtain the joining values for the multi-table join, cell A2 and its homocells have been set as the master cells in the three esCalc worksheets.

Perform the join operation by joining the absence data, the performance and bonus data to the first table:

esCalc_excel_multi_table_7

Finally, calculate the payable weekly salary:

esCalc_excel_multi_table_8

With esCalc, the desktop BI software, you can achieve many advanced computations, such as the multi-table join, through some very basic operations. In this sense, everyone can be an expert in data computation.