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.



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:


Then, calculate the weekly wage payable:


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.



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:


Finally, calculate the payable weekly salary:


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.