Figure 895 Change from previous day.

In this figure, the percentages in C16:C18 express the revenue as a percentage of the total sector revenue in B15. The confusing part is that the 14.35% in C15 shows how the Communications sector total of $962K compares to the grand total of $6.7 million. All of the percentages are correct, it is just strange to see a smaller number on the total line than on the detail lines.

3

PIVOT RANKS DON’T MATCH RANK()

Problem: I set up a pivot table and showed the values as a rank, using Rank Largest to Smallest. Why is the fourth product assigned a rank of #3?

Figure 896 Why is C7 assigned a rank of 3?

Strategy: As if there is not enough controversy in the Excel ranking world, Excel came up with yet another way to handle ranking with pivot tables. The issue always centers around any ties and how the subsequent values are numbered.

Typically, if you have two values tied at #2, the next value would be assigned a rank of 4.

Starting in Excel 2010, the RANK.AVG would assign the tied values a 2.5, and assign the next item a rank of 4.

Pivot tables do something different, assigning both of the tie values a 2, then going to #3 for the next item.

If you need one of the methods shown in E:G, plan on adding a calculation next to your pivot table instead of using the built-in rank.

CALCULATED FIELDS IN A PIVOT TABLE

Problem: I need to include in a pivot table a calculation that is not in my underlying data. My data in- cludes quantity sold, revenue, and cost. I would like to report gross profit and average price.

Strategy: You can add a calculated field to a pivot table. Follow these steps:

1. Build a pivot table with Product and Revenue columns.

2. The Calculated Field command is found under the Fields, Items, and Sets menu.

Figure 897 Choose Calculated Field.

3. In the Insert Calculated Field dialog, type a field name such as Profit in the Name text box. In the

Formula text box, type an equals sign. Double-click the Revenue entry in the Fields list. Type a minus sign. Double-click the COGS entry in the Fields List. The Formula text box should say =Rev- enue-COGS. Click the Add button to accept this formula.

Figure 898 Add a new formula.

4. Add the following formula for GPPct: =Profit/Revenue.

5. Add the following formula for AveragePrice: =Revenue/Quantity. 6. Click OK to close the Insert Calculated Field dialog box.

Results: The resulting pivot table will include all the fields.

Figure 899 Excel adds the new fields to the pivot table.

Gotcha: The label Sum of GPPct is somewhat misleading, as is Sum of Average Price. In reality, Excel finds the sum of Revenue, finds the sum of Quantity, and then divides the values on the total line in order to get the average price. This makes calculated fields fine for any calculations that follow the associative law of mathematics. Having Excel do all the individual average prices and then sum them up would be impossible in a pivot table unless you are using Power Pivot.

You can rename the fields that have misleading headings. Simply click on the heading and type a new heading.

Gotcha: It is possible to use an Excel function in the Insert Calculated Field dialog. However, the function is applied to individual rows instead of using the population of matching rows. In the figure below, column I contains a calculated field of MEDIAN(Score). To calculate this, Excel takes the MEDIAN of cell B2. Of course, the median of B2 is the value from B2. They repeat this for each cell, then sum up the results. This is not a median at all. It is the same as the sum of the cells. To truly calculate a median, you will need Power Pivot.

3

Figure 900 Using functions in calculated fields may not work as you want.

ADD A CALCULATED ITEM TO GROUP ITEMS IN A PIVOT TABLE

Problem: I’m working with the small data set shown here.

Figure 901 The initial data set.

My company has three product lines. The Cocoa Beach plant manufactures ABC and DEF. The Marathon division manufactures XYZ. I have a pivot table that shows sales by product. Remember that the total of items sold is 127.