My Excel Files

Microsoft Excel & VBA Tutorials

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2 other followers

Using a Pivot Table to Calculate a Weighted Average

Posted by Steven Taylor on August 4, 2010

Lets assume that your data looks something like this:

-                  A                           B          C         D

1

2

3          Grades                  Cost        Staff     Weight

4          Grade A          10,000            5

5          Grade B          12,000            3

6          Grade C          14,000            2

In order to create a pivot table with a weighted average you will need to do the following:

  1. In cell D4 of the above you would enter the following formula: B4 * C4.
  2. Copy the formula you just created down to cell D6.
  3. From Excel’s menu bar, click on Data
  4. From the menu that drops down, click on PivotTable and PivotChart Report
  5. The Pivot Table wizard starts up

  1. Click the Next button at the bottom.
  2. Here you need to select the data for your pivot table. Using the data from above this would be equal to A3: D6. Once the range is selected, click next.

  1. Click layout on the bottom left
  2. Drop the Grades into the “rows” selection and Staff in the “data” section.

10.   Click ok and then finish

11.   Now you will have a pivot table but you still need to add the weighted average calculation to the table.

12.   The weighted average calculation is added to the pivot table by using a calculated field.

13.   To create a calculated field you need to open the pivot table toolbar. If the pivot table toolbar does not automatically appear when you select your pivot table you can access it from top Excel menu bar under View > Toolbars > Pivot Table.

14.   Click the down arrow next to the word PivotTable at the left side of the PivotTable toolbar. Excel displays a menu.

15.  Choose Formulas | Calculated Fields. Excel displays the Insert Calculated Field dialog box.

16.  In the Name box, enter a name for your new field for example “Weighted Average”.

17.  In the Formula box, enter the formula you want used for your weighted average, such as =Weight/Staff.  Note you can use the field names from the field list at the bottom of the Insert Calculated Field dialog box.

18.  Click OK.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: