If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
excel chart - sales data for 3 years
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 |
Thread Tools | |
Display Modes | |
|
|