Payroll Calculation in Excel – Differential Piecework with Multiple Scales – How To

Differential piece work wage payments with multiple rate brackets or scales

Excel Skill Level: Intermediate [It is strongly recommended if you are new to excel then go through previous tutorials in the series]

For the purpose of this tutorial you need to download this excel file

In our previous examples things were simpler as we were looking for a single slab in which output of the employee falls and a single rate was applied to the units produced to compute wage. But in many cases we have to assess the output against multiple scales or brackets. Look at the following data:

In this data for units produced 0-100 rate is 3 for units in addition to 100 but up to 200 rate is 5 and so on. That is we have to calculate wage pertaining to each bracket separately as there is no single rate applied to output. For example Fareed produced 350 units. In his case his wage will be calculated as follows:

To work out this situation we only have to do few additional steps, little tweaks in our function use and we will excel at this situation as well. So lets do it!

Preliminary steps

We need to convert the data in to a shape which we can use conveniently. We following additional things:

1. one column that tells Start of range
2. Second column that tells end of range
3. Third column that tells units produced in previous slab
4. Fourth column that calculates incomes earned in the previous slabs

Understanding the approach:

1. Having units produced known, we want to find out in what slab it falls. This is taken care by lookup function.
2. Once that is known, we want to know what income is carried forward from previous slab. That is taken care by having fourth column in place in which values are carried using the information from third and second column.
3. Then we want to calculate additional number of units produced above the last applicable slab and multiplying that with respective per unit rate. That is done by having third column in place.

Step-by-step – Adding Columns

Step 1: Add two columns between Slab and Rate columns by right clicking Excel column header and selecting insert. Repeat this process twice to add two columns. Name one as Start and the other as End

Step 2: Add two columns after Rate column. Name one units made in previous slabs and the other wage earned in previous slabs.

Step 3: In the first row of Start column type 0 (zero) and in the End column type 100.

In the file column has already been added for you. So continue to Step 4.

Step 4: In the second row of End (cell C6) put a formula: =C5+100. Press enter key and drag it down to fill till the end of range. In the second row of Start column (cell B6) type this formula: =C5+1. Press enter and drag it down till the end of range or simply double click the fill handler.

Step 5: In the “Units produced in previous slab” in cell in cell E6 write formula: =C5. Drag down the content to the end of range or double click on the fill handle.

Step 6: In the “Wage earned in previous slab” column in cell F6 put this formula: =E6*D5

Step 7: In cell F7 put this formula: =F6+(E7-E6)*D6. This formula calculates the cumulative wage earned so far from previous slabs.

Name the Data set – Learning on the way!

In above four steps we have created a minimal data set which we will be using over and over again. And for that purpose we will have to select this whole data number of times. If we somehow give this range an identity so that we can easily refer to this range easily we will not have to make the range selection again and again. We will simply put the name of range in the formula and Excel will understand of what data set we are talking about. This is achieved by “Naming” feature of Excel.

Step 1: Select the data enclosed inside columns “Start” and “Wages earned in previous slabs”.

Step 2: Hit Ctrl+F3 and Names manager dialogue box will open. Click New button at the top. And in the Name field type: wagedata and Click OK button.

Step-by-step

Locating the previous income earned

Our concerned data is just below the wage data range. Where names of employees, units produced and wage payable column are present.

Step 1: In cell C19 press = sign and excel will enter editing mode. Start typing VLOOKUP, Excel will provide suggestions. Select VLOOKUP from the list and press TAB. You will have: =VLOOKUP(

Step2: Select the cell that contain the units produced and press comma button once.

Step3: Type wagedata. Remember wagedata is the name we gave to the data set we created for computation purpose. Hit comma.

Step 4: As wage from previous slab is in the fifth column of wagedata so press 5. Close parenthesis

Computing units produced over and above the previous slab

Step 5: Press plus sign and open round bracket. Select cell containing units produced again. Put a minus sign

Step 6: Type wagedata again, hit comma and this time in column index enter 4 as units from previous slab are in column three of selected range. Close parenthesis twice. One for VLOOKUP and the second for computing the additional units first.

Step 7: Above steps help us calculate additional units produced above previous slab. Now we need to multiply additional units with appropriate rate. Put asterisk (*) and move to following step

Locating the correct rate

Step 8: Start typing VLOOKUP, press TAB key once to insert VLOOKUP function

Step 9: Select the cell containing number of units produced. Hit comma and type wagedata. Hit comma again and as rate is in the third column of wagedata range so put 3. Close parenthesis and hit enter.

Your formula will look like this finally:

=VLOOKUP(B19,wagedata,5)+((B19-VLOOKUP(B19,wagedata,4))*VLOOKUP(B19,wagedata,3))

BOOM! Your wage is calculated and you have just excelled to whole new level of Excel as you have learned numerous features along the way! 🙂

Now drag down the content for other employees or simply double click the fill handle and wage for other employees will also be calculated.