Grouping daily records by week for chart display

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

I have a table of daily sales transactions, but need to chart the data
by week (and month). I will be showing the sum of sales for a given
week as a single data point.

Is there a simple way to do this in creating the chart, or do I first
need to pivot the data and then create the chart?
 
Hi Mike,

You can use the Pivot Table tool to do this or you can do it in the
spreadsheet with a regular chart. You also have the option to use the pivot
table as the source for a non-pivot table chart. The real question is which
method do you want?

I will illustrate one of the solutions, if you want one of the other ones
let us know.
Suppose the dates are in the range A2:A40 and the sales in B2:B40. Then in
another column enter the first day of each week, for example, in G4 I
entered:

1/1/2004
1/8/2004
1/15/2004
1/22/2004
1/29/2004
2/5/2004
2/12/2004
2/19/2004
2/26/2004
3/4/2004
3/11/2004
3/18/2004
3/25/2004
4/1/2004
4/8/2004
4/15/2004

G5 contains the formula G4+7.
In H4 I enter and copy down the formula:

=SUMPRODUCT(($A$2:$A$40>=G4)*($A$2:$A$40<G5)*$B$2:$B$40)

Cheers,
Shane Devenshire
Microsoft Excel MVP
 
Back
Top