Pivot table formulas

[Tim Wheeler]

Can anyone tell me if there is a function for calculating moving annual
totals (MAT) as a calculated item in a pivot table. Thanks Tim

 

[Roger Govier]

Hi Tim

I'm afraid there isn't any inbuilt method.
Assuming you have your data for each Month as say 2007-01, 2007-02 etc. or
2007-Jan, 2007-Feb and you allocate this field as a row item, then put your
Value field as a Data item. My fields are titled Month and Value
respectively. Amend any formulae below to reflect the names you have used.

This will give you each of the months data for say 36 months, and alongside
a cumulative figure to that Month.
In a column to the right of the PT, (column G in my case), alongside the
first row of data, enter a GetPivotData formula like
=IF(E5="","",GETPIVOTDATA("Sum of Value2",$E$3,"Month",E5))
Your column and cell values may be different, depending upon where on the
page your PT is created.
E5 in my case is the cell where 2007-01 appears as the Row value.
Copy that formula down as far as required.

in column H, in cell H5 enter the following formula
=IF(ROW()<17,SUM($G$5:G5),SUM(INDEX(G:G,ROW()):INDEX(G:G,ROW()-11)))
and copy down as far as required

You can now hide the column with the repeat of the month's data (column G)
and you will have 2 columns of data, one being the Month value, the other
the Rolling 12 month value.

If your Month fields are titled 2007-Jan etc, then you will need to copy the
column of row names, and paste to another location.
Mark the range of valid month names then choose Tools>Options>Custom
Lists>Import
Double click your Month header>Advanced>Sort>Ascending>by Month

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
Moving Annual Total, by month.
A Bear.
http://www.mediafire.com/file/yay5yotdjmo/04_21_09.xlsx

 

[Tim Wheeler]

Hi Roger

That's really useful - thank you.

Kind regards
Tim

 

[Tim Wheeler]

Dear Hernert

Thank you for your help. I assume that this functionality is only available
with Excel 2007?

Kind regards
Tim

 

[Roger Govier]

Hi Tim

you can use Herbert's elegant solution in earlier versions of Excel.
With the source data set up as he has shown, (but not as a table), then the
formulae would become
D5
=DATE(YEAR(Sheet1!$B$5:$B$119),MONTH(Sheet1!$B$5:$B$119),1)
E5
=COUNTIFS(Sheet1!$D$5:$D$119,Sheet1!$D$5:$D$119)
F5
=SUMPRODUCT((Sheet1!$C$5:$C$119)*
(Sheet1!$D$5:$D$119=EDATE(Sheet1!$D5,{0,-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11})))
/Sheet1!$E$5:$E$119

Clearly these formulae are using fixed ranges, the size of Herbert's source
data.
You would be better creating Dynamic Named ranges
Insert>name>Define
Name lrow
Refers to =COUNTA(Sheet1!$B:$B)
Name DDate
Refers to =Sheet1!$B$5:INDEX(Sheet1!$B:$B,lrow)
Name Amt
Refers to =Sheet1!$C$5:INDEX(Sheet1!$C:$C,lrow)

Repeat for the other column names, and substitute the names in place of the
fixed ranges in the formulae.
(I have written some code for generating dynamic named ranges for each used
column in a workbook, which can be downloaded from
http://www.contextures.com/xlNames03.html)

Be aware, that you also need to use Tools>Addins>Analysis Toolpak, in order
to have the EDATE function available to you.

 

[Herbert Seidenberg]

Roger,
Thanks for your kind words and
for translating my formulas to
the ancient Excel formats.
Here is the edited file in Excel 2003:
http://www.mediafire.com/file/jiemlmoziqm/04_21_09.xls

 

[Tim Wheeler]

Gentlemen

I really appreciate the help both of you have given me to help solve my
problem.

Thank you,
Tim

 

[Roger Govier]

Hi Herbert

I didn't do any work in translating, I just converted the Table back to a
range and it produced those formulae.
I never actually tried it in XL2003.
How strange, that Edate will work within SP in XL2007, but not in XL2003 -
something of which I was unaware.
Yet again, you have come up with an excellent workaround.

Of course, with Lists in XL2003 you don't have to worry about creating
dynamic ranges, but anyone using XL2002 and earlier would be well advised to
do so.

I do enjoy loading your solutions and examining them. Thank you.