G
Guest
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.
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.