I have three years' worth of sales data, by transaction.

Example:

1/2/2002 5
1/2/2002 2
1/2/2002 3
1/3/2002 4
1/6/2002 2

And so on.

What I would LIKE would be a chart showing:

WEEK 2002 2003 2004
1 14 xx yy
2 2 xx2 yy2
3

and so on.

I know WEEKNUM returns the week number, but it shows the same value ("1")
for 1/2/2002, 1/2/2003, etc.

So how do I combine the data to give me the week of each year, and then lay
it over a table??????

This is what you get for being a Lit Major!

Hi
I would start using a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...ble/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany
"RJB" schrieb im Newsbeitrag
...
I have three years' worth of sales data, by transaction.

Example:

1/2/2002 5
1/2/2002 2
1/2/2002 3
1/3/2002 4
1/6/2002 2

And so on.

What I would LIKE would be a chart showing:

WEEK 2002 2003 2004
1 14 xx yy
2 2 xx2 yy2
3

and so on.

I know WEEKNUM returns the week number, but it shows the same value ("1")
for 1/2/2002, 1/2/2003, etc.

So how do I combine the data to give me the week of each year, and then
lay
it over a table??????

This is what you get for being a Lit Major!

Make a couple of helper columns. One is Year, with the formula =YEAR(xx), and the
other is Week, with the formula =WEEKNUM(xx), where xx is the date in the same row.
Put headers on each column (Date, Value, Year, Week). Select the range and construct
a pivot table (data menu). When laying out the pivot table, drag Year to the Columns
area, Week to the Rows area, and Value to the Data area (by default Excel uses Sum
of Value here).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

RJB wrote:

I have three years' worth of sales data, by transaction.

Example:

1/2/2002 5
1/2/2002 2
1/2/2002 3
1/3/2002 4
1/6/2002 2

And so on.

What I would LIKE would be a chart showing:

WEEK 2002 2003 2004
1 14 xx yy
2 2 xx2 yy2
3

and so on.

I know WEEKNUM returns the week number, but it shows the same value ("1")
for 1/2/2002, 1/2/2003, etc.

So how do I combine the data to give me the week of each year, and then lay
it over a table??????

This is what you get for being a Lit Major!

Thanks, guys. Both suggestions were helpful. I have one more quandary....

I have a few weeks out of each year where I have NO sales...

For example, in Week 31 02, 03, and 04, I have nothing... So my table skips
from Week 30 to Week 32. I'd rather it put in a Week 31. Short of artifically
plugging the source spreadsheet, any thoughts?

HAVE:

WEEK 2002 2003 20004
30 52 68 71
32 39 66 42

WANT:

WEEK 2002 2003 20004
30 52 68 71
31 0 0 0
32 39 66 42

The pivot table options includes showing or hiding cells with no values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

RJB wrote:
Thanks, guys. Both suggestions were helpful. I have one more quandary....

I have a few weeks out of each year where I have NO sales...

For example, in Week 31 02, 03, and 04, I have nothing... So my table skips
from Week 30 to Week 32. I'd rather it put in a Week 31. Short of artifically
plugging the source spreadsheet, any thoughts?

HAVE:

WEEK 2002 2003 20004
30 52 68 71
32 39 66 42

WANT:

WEEK 2002 2003 20004
30 52 68 71
31 0 0 0
32 39 66 42