excel chart - sales data for 3 years

Hi i need help to creat a weekly sales graph for 3 years. I have
complete weekly data (52 weeks) for year 2002 and 2003. Now current
year 2004 sales come in on a weekly basis. Here is what I need:

1. A triple line graph with dollars on X-axis and week number and
period name on Y-axis .

2. the twist to this is I would like to show a flyer drop on the line
when that happens during the year. The flyer drop is random.

Please help.

Thanks,

Rashid.


data:
period week sales-2004 sales-2003 sales-2002 flyer
jan wk1 1000 1100 900 yes
jan wk2 950 850 975 no
jan wk3 1100 950 900 no
jan wk4 800 975 1000 no
feb wk5 1200 1000 950 yes

Rashid -

This should be done in two parts. First, set up a data range like this:

period week Label Blank
jan wk1 jan-wk1 0
jan wk2 jan-wk2 0
jan wk3 jan-wk3 0
jan wk4 jan-wk4 0
feb wk5 feb-wk5 0

The label column is a concatenation of the first two columns. Select the
Label and Blank columns and make a bar chart. The Labels go up the left
axis, and the bars do not appear because their value is zero. Double
click on the left axis, and on the scale tab, check the Categories in
Reverse Order box, so jan-wk1 is at the top and feb-wk5 at the bottom.
The horizontal axis switches to the top, which is okay.

Now set up another data range like this. It could be in the same rows as
the first range, in the columns to the right.

sales-2004 sales-2003 sales-2002 Altitude
1000 1100 900 4.5
950 850 975 3.5
1100 950 900 2.5
800 975 1000 1.5
1200 1000 950 0.5

Add a new series as follows. Select the cells below sales-2004, hold
down Ctrl and select the cells below Altitude, and select Copy from the
Edit menu (or press Ctrl-C) to copy this discontiguous range. Select the
chart, choose Paste Special from the Edit menu, choose the New Series
and Categories in First Column options, but not Replace Existing
Categories or Series Names in First Row. Excel adds the new series as a
bar chart, like the first (which is hidden). Right click on this new
series, choose Chart Type from the pop up menu, and change it to an XY
Scatter type, with or without connecting lines. A new axis appears on
the right. Double click on it, and on the Scale tab, uncheck the Value
(X) Axis Crosses At Maximum box. The X value axis now appears along the
bottom of the chart. We're getting close.

Copy the discontiguous range for sales-2003 and Altitude, select the
chart, and use Paste Special as above. Excel remembers that the last
series we pasted was changed to an XY Scatter series, so that's how it
pastes the new one, saving us a few steps. Copy the contiguous range for
sales-2002 and Altitude, and do the Paste Special step once more.

Now you just have to clean it up. Double click the right side axis, and
on the Patterns tab, select None for Major and Minor Ticks and for Tick
Mark Labels. Do the same for the top side axis. Use the Source Data
command on the Chart menu, Series tab, to add the applicable sales-2004,
sales-2003, or sales-2002 label to each series as its name.

One way to indicate the flyer drop is as follows. Instead of all zeros
in the Blank column, way up in the beginning of the process, put ones
where the flyer column has "yes". Now the initial bar series isn't
completely invisible, but it has some bars that stick out most of the
chart's width. Before hiding the primary value axis (the top side axis),
set its scale to 0 min and 1 max, so the visible bars span the entire
chart. Double click on this bar series, pick some appropriate pattern
formats (I used a light fill color and no border), then on the Options
tab, set the gap width to zero. If you change the column header from
Blank to Flyer, it will be a good descriptive label in the Legend.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Rashid wrote:

Hi i need help to creat a weekly sales graph for 3 years. I have
complete weekly data (52 weeks) for year 2002 and 2003. Now current
year 2004 sales come in on a weekly basis. Here is what I need:

1. A triple line graph with dollars on X-axis and week number and
period name on Y-axis .

2. the twist to this is I would like to show a flyer drop on the line
when that happens during the year. The flyer drop is random.

Please help.

Thanks,

Rashid.


data:
period week sales-2004 sales-2003 sales-2002 flyer
jan wk1 1000 1100 900 yes
jan wk2 950 850 975 no
jan wk3 1100 950 900 no
jan wk4 800 975 1000 no
feb wk5 1200 1000 950 yes