Pivot table formulas
 

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

Pivot table formulas
 

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()):IND EX(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 ToolsOptionsCustom
ListsImport
Double click your Month headerAdvancedSortAscendingby Month

--
Regards
Roger Govier

"Tim Wheeler" wrote in message
...
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

Pivot table formulas
 

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

Pivot table formulas
 

Hi Roger

That's really useful - thank you.

Kind regards
Tim

"Roger Govier" wrote:

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()):IND EX(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 ToolsOptionsCustom
ListsImport
Double click your Month headerAdvancedSortAscendingby Month

--
Regards
Roger Govier

"Tim Wheeler" wrote in message
...
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

Pivot table formulas
 

Dear Hernert

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

Kind regards
Tim

"Herbert Seidenberg" wrote:

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

Pivot table formulas
 

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
InsertnameDefine
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 ToolsAddinsAnalysis Toolpak, in order
to have the EDATE function available to you.

--
Regards
Roger Govier

"Tim Wheeler" wrote in message
...
Dear Hernert

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

Kind regards
Tim

"Herbert Seidenberg" wrote:

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

Pivot table formulas
 

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

Pivot table formulas
 

Gentlemen

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

Thank you,
Tim

"Herbert Seidenberg" wrote:

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

Pivot table formulas
 

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.

--
Regards
Roger Govier

"Herbert Seidenberg" wrote in message
...
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