grouping data by month not year

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have payment data that is entered by date (MM/DD/YYYY). I would like to
view this data in a chart by month (ex. Jan 2006 vs Jan 2007 vs Jan 2008).
Is there an easy way to do this?
 
If you subtotal your data by month, then collapse the outline to hide the
detail and just show the monthly totals, you can create a chart of that.

Tyro
 
Brian -

If the dates are Excel serial dates, maybe you could use helper columns for
=YEAR and =MONTH, and then use the Pivot Table group feature to aggregate
multiple transactions each month (if there are some). Finally, create a line
chart, maybe like the one shown on the Visual Check Seasonality worksheet in
the following workbook (which did not use the method I described above)
http://www.mikemiddleton.com/LinearTrendSeasonalForecast.xls

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel
 
Brian,

One way is to set up a table somewhere with formulas that yield the month totals you want
graphed.

In this example, the dates are in column A, and the amounts in column B. This formula will
yield the totals for month 1 (Jan):
=SUMPRODUCT((MONTH(A2:A7)=1)*(B2:B7))

Then you'd put similar formulas for the other months. It could be expanded to "roll" the
months if needed. Build the chart from this table. The chart will always have the current
monthly totals, without manual intervention.
 
Back
Top