Pivot Table - Running Total Annualised
 

Just discovered the "Group" feature in Pivot tables, as applied to dates, and
it's brought me tantalisingly close to what I want.

I have a list that comprises 4 years worth of sales, each row being the date
a sale was made, and the amount of the sale. What I wanted was an Annualised
total for each month (i.e. all sales in the last 12 calendar months). I've
found "Running Total", but if I group the date by year and month, the running
total resets to 0 every time the year changes. If I just group by month, then
it combines all the years, producing a useless conglomeration of figures.

I've also found a trendline that can be applied to a Pivot Chart (where I'm
just recording totals per month), that averages over 12 periods. This gives
me approximately the shape of the line I want, but figures that are average
sales - not total.

Is there any way to adapt either of these methods to produce the effect I
want? I know there are plenty of other ways I could structure the data, but I
want to apply the technique to different lists of sales that will be supplied
from various sources, and want something that can be put straight on top of
other people's data without complicated reformatting.

Any suggestions gratefully received.
Geoff.

Geoff,

If I just group by month, then
it combines all the years, producing a useless conglomeration of figures.

You can manually set the beginning and end dates of the grouping: the only
drawback is that you will need to manually sort to put Jan (of this year)
after Dec (of last year).

HTH,
Bernie
MS Excel MVP


"Geoff C" wrote in message
...
Just discovered the "Group" feature in Pivot tables, as applied to dates,
and
it's brought me tantalisingly close to what I want.

I have a list that comprises 4 years worth of sales, each row being the
date
a sale was made, and the amount of the sale. What I wanted was an
Annualised
total for each month (i.e. all sales in the last 12 calendar months).
I've
found "Running Total", but if I group the date by year and month, the
running
total resets to 0 every time the year changes. If I just group by month,
then
it combines all the years, producing a useless conglomeration of figures.

I've also found a trendline that can be applied to a Pivot Chart (where
I'm
just recording totals per month), that averages over 12 periods. This
gives
me approximately the shape of the line I want, but figures that are
average
sales - not total.

Is there any way to adapt either of these methods to produce the effect I
want? I know there are plenty of other ways I could structure the data,
but I
want to apply the technique to different lists of sales that will be
supplied
from various sources, and want something that can be put straight on top
of
other people's data without complicated reformatting.

Any suggestions gratefully received.
Geoff.

In the source data, add a field that calculates the first of the month
for each sale. Name the field, e.g. YearMonth, and use the following
formula in the data rows:

=DATE(YEAR(C2),MONTH(C2),1)

where the date is in column C.

Add that field to the pivot table, and use it as the base field for the
running total

Geoff C wrote:
Just discovered the "Group" feature in Pivot tables, as applied to dates, and
it's brought me tantalisingly close to what I want.

I have a list that comprises 4 years worth of sales, each row being the date
a sale was made, and the amount of the sale. What I wanted was an Annualised
total for each month (i.e. all sales in the last 12 calendar months). I've
found "Running Total", but if I group the date by year and month, the running
total resets to 0 every time the year changes. If I just group by month, then
it combines all the years, producing a useless conglomeration of figures.

I've also found a trendline that can be applied to a Pivot Chart (where I'm
just recording totals per month), that averages over 12 periods. This gives
me approximately the shape of the line I want, but figures that are average
sales - not total.

Is there any way to adapt either of these methods to produce the effect I
want? I know there are plenty of other ways I could structure the data, but I
want to apply the technique to different lists of sales that will be supplied
from various sources, and want something that can be put straight on top of
other people's data without complicated reformatting.

Any suggestions gratefully received.
Geoff.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html