How to mix apples & oranges?

The term “mixed apples and oranges” is used to describe attempts to link some completely incompatible things. In Excel this often refers to making a strange conglomerate of a Pivot and traditional table. I personally never recommend it, but there are times when such reports are still necessary. With some ingenuity and little  help of GETPIVOTDATA and OFFSET functions we’ll be able “to mix apples and oranges”. None of the above were hurt during the process!

The traditional scenario: you have a Pivot table with actual sales, and you want to enter the value of the budgeted sales to compare these two parameters and calculate the variance. To create this report, first you need to create Pivot table with actuals data. When you create it there are no more changes! Right from the Pivot table should add column with budgeted values. Then we add another empty column for calculating variances, and then another column with index values from 0 to n (n corresponds to the index of the last row in the table).

How to calculate the variance? First, go to the cell under the “Variance” heading, then click on the value of sales shown in first row of Pivot Table. Excel will automatically generate GETPIVOTDATA function. Then add the symbol for division, click on the cell that contains the value of the budgeted sales for first item and end up entering the formula.

In traditional Excel tables would be enough to copy the formula into the remaining cells of the column and report would be completed. In this case this is not possible, because the function GETPIVOTDATA references to a particular cell in a Pivot table. Let us analyze the formula:

=GETPIVOTDATA(“SALES”,$A$3,”Item”,”Cabernet Sauvignon”)/C4

Function GETPIVOTDATA first takes the value of SALES, and the second parameter ($A$3) refers to the Pivot table. Next we can see that data are taken for “ITEMS” and a particular item that has value “Cabernet Sauvignon”. To automatically update the formula Excel should change the names of the wines, right? We can achieve that by using OFFSET function:

=OFFSET($A$3,1,0,1,1)

This formula marks one cell down refering to $A$3. Since we want to automatically update the formula we will add a reference to index column:

=OFFSET($A$3,E4+1,0,1,1)

At the end, all we need to do is to add this formula instead “Cabernet Sauvignon” in GETPIVOTDATA function:

=GETPIVOTDATA(“SALES”,$A$3,”Item”,OFFSET($A$3,E4+1,0,1,1))/C4

Such formula should be further copied to the remaining cells in a column.

In the end, all that is left to hide column E, as well as to “beautify” the report and common users won’t be able to perceive the difference: they will see one report instead two of them combined. Finally, do not forget one important thing: these reports make sense as long as you do not change the initial Pivot Table.