Grouping daily records by week for chart display

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?
 
S

Shane Devenshire

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
 
Top