New versions of Excel contain an evil feature called GETPIVOTDATA. Here’s a screencast (Windows Media only for now) to show you an example:
These results are bad because they don’t change when you copy and paste formulas. The user interface problem is that you commonly create a formula then copy/paste it down to propogate that formula. You expect relative references to be the default. The numbers in the PivotTable look just like any ordinary data, so you expect to be able to build formulas using them. However, GETPIVOTDATA treats your data like absolute references, which means that certain numbers will be managed in one mode and others in another mode.
This breaks with your experience and generates results you don’t expect. When you catch them, that is! If you don’t catch the results, you can be publishing numbers that are just plain wrong.
Here’s how to fix this.
Right click on the toolbar and go to Customize…
Go to the Commands tab and select the Data category.
Find the Generate GetPivotData button (it’s about 90% of the way down) and drag it into one of your toolbars
Make sure that button is turned off.