In Sections 3.10 and 3.11, I learned about Running Totals. The examples used a running total of monthly quantities to calculate a year-to-date total. That is, each month's running total contained a different number of months. (One month [Jan] in January; two months [Jan + Feb] in February and so on into the night.) What I would find vastly more useful is a 12-month running total, that is, the latest month summed with the previous eleven. No matter what month, there will always be the latest 12 months in the sum. Is there a way to do that?
(I want to use the pivot table to track the emissions of pollutants into the atmosphere from various industrial processes. Permit limits are frequently written as 12 consecutive-month running totals.)
Thanks for your help!
Hello Tracy,
You can certainly do this but you will need to add an OFFSET formula outside the Pivot Table to get this moving Running Total.
I have attached the workbook with the Offset solution for you to look at.
When you add new data at the bottom of your Excel Table, all you have to do is hit the Refresh button and the Pivot Table and Formula will get updated.
Note that I converted the Pivot Table into a Tabular Format (Design > Report Layout) and with No Subtotals (Design > Subtotals) for this trick to work properly.
I hope this helps.
Thanks,
John Michaloudis
