Determining the Average Voluntary Deduction per check

Refresh using the desired data range


Click on the Payroll History Pivot Table.


The information that we will need on the pivot table is the check number ( so that we can count the number of checks) and then the Net Gross and the Voluntary Ded amount.

Our pivot table in the original view has many fields on the pivot table. 

We want to remove the following items from the pivot table:




Site Loc

Job ID

Full Name

Full Acct Code


To remove the fields, click on the heading for the column and drag to the upper portion of the table where the additional field names are listed.



Add the Check Number to the pivot table. You will notice that the check # does not appear in the list of available fields on the pivot table. To see additional fields, right click anywhere in the pivot table and select Show Field List.


The field list will appear on the right hand side of your screen.  Scroll through the field list at the top and locate Check #.  Note that fields listed in bold are already located in the pivot table.


Drag the check number to the left most column in the pivot table.


In the data area on the pivot table, we need to remove all data fields except the Payline Gross and then we will add in the Voluntary Ded amount.



Remove:

Payline Gross

Net Pay

Total Hours

Total Hourly Pay

Amt. Escrowed

You can remove these by clicking on the header and dragging up and to the right until you see the red X and then let go.




Your table should now look similar to this.


To add in the Voluntary Ded amount, scroll through the field list (which should still be open on the right side of the screen).



Drag and drop the Vol Deds into the Data Area (not on the header area).




Your table should now look like this.


Format the Vol Deds column by right clicking on the Vol Deds header and selecting Field Settings.



Right click somewhere in the data under the Vol Deds and select Value Field Settings (or Field Settings if office 2003).


Click on the Number format button


Select Currency and click OK twice.


In order to calculate the average voluntary deduction amount for the checks, we will need a count of the checks.


Click on the Pivot Table and then Select PivotTable Tools from the top part of your menu and then select the Options tab. Click on Formulas




Select Calculated Field.



Type CheckCount for the name of the field. The formula should be =1.


In order to make the Check Count field a running total, right click in the Check Count Column on the pivot table and select Value Field Settings.


Click on the "Show Values As" tab.



Select “Running Total In” for the Show values as drop down option.



Select Check # in the base field.



This indicates that the check count should be a running total based on the check number – so this will in essence count the checks.


Your pivot table should look similar to this.


Scroll to the bottom of the pivot table (hint, use Ctrl-End-down arrow).


Place your cursor in a blank field somewhere to the right of the totals.  Type = to begin a formula.  We are going to take the sum of the vol deds column and divide by the last count.


Type = and then click on the grand total number for the vol deds column.  Then type / (for divided by) and then click on the last count number for the last check.  Press enter.  Excel may ask to correct your formula.  Click OK.




The answer to the formula will appear. This is the average amount of voluntary deductions per check!