PLeeeeease help. How to do combo line-column chart

[rdionne]

I need to display vertical lines in my column chart to mark various limits. I know how to do the column chart programmatically, but how do I add the line chart and make vertical lines with it? Is it possible?

 

[Mark W.]

1. Select the Chart Area and reset the chart type of the entire chart area (Format Chart Area) to XY Chart.

2. Select any 1 of the histogram values and set the chart type to Column.

3. Select a single value of your vertical line and re-Format Data Series with the addition of a line pattern.

 

[rdionne]

Thanks for hanging in there with me. I'm using Excel 2000 on a PC. If I select the chart area and choose Format Chart Area, I get a window that has 3 tabs: Patterns, Font, and Properties. I can select the chart and choose "Chart Type" from the top menu. No problem doing step 2 - selecting a data point and choosing the column chart type. When I do that, my line series moves to the left side of the chart. re-formatting the series with the addition of a line pattern doesn't move the line data from the left side of the chart to where it should be. The two different charts just don't share the same X axis. I'm not sure what I'm doing wrong, but it just won't work for me. What version of Excel are you using?

 

[Mark W.]

I stand corrected...

1. Select the Chart Area and reset the chart type of the entire chart area to XY Chart.

2. Select any 1 of the histogram values and set the chart type to Column. This should not change the chart type of your vertical line values. But, if it does begin again at Step 1.

3. Select a single value of your vertical line and re-Format Data Series with the addition of a line pattern.

I use both XL2000 and XL97.

 

[Mark W.]

BTW, the SERIES function for your vertical lines can have values (as shown below) as its arguments instead of cell references...

=SERIES(,{3,3},{0,30},2)

 

[rdionne]

Mark,
This just doesn't work for me. As soon as I change my histogram data series to a column chart, the other data series stays as XY but it shifts position to the left, using it's own axis. I have 31 data points for my histogram data. They are evenly spaced on the X axis from -0.2 to 2.3 . The Y values go from 0 to 200. For example, for a vertical line at 1.3 (about the middle of the chart), the data has 2 points (1.3,0) and (1.3,200). I'm not really clear what you mean by reformating the data series with the addition of a line pattern. I'm trying to use the XY chart type "Scatter with data points connected by lines without markers". This "connects the dots" for me. I assume that step 3 is used to connect the dots on a standard XY scatter type chart. Let me describe my data in more detail:
A$1 to A$31 contains my X axis values. B$1 to B$31 contains my histogram data (frequency) of each bin which ranges from 0 to 200.
C$1 and C$2 contain the same value, this is the data for the position of the line on the X axis. D$1 contains 0, and D$2 contain 200, so this should draw a line vertically from the bottom of the plot area to the top. To create the chart I first select the XY chart type. Next I add a new series. I use A$1:A$31 for the X values and B$1:B$31 for the Y values. I then add another series and use C$1:C$2 for the X values and D$1:D$2 for the Y values. What am I doing wrong?

 

[Mark W.]

Questions...

1. Using the Chart | Source Data... menu command what's the range for the "Category (X) axis labels" (see the Series tab) for your wayward vertical line? It should be the same as your histogram values.

2. After right-clicking the Chart Area choose Chart Options... from the popup menu. Is a secondary X-axis checked on the Axis tab? If so, uncheck it.

I'm not experiencing your problems. There must be something you're doing differently. Let's figure it out.

 

[rdionne]

1) Both series have the same category axis labels: =HistChartData!$A$1:$A$31
2) Secondary axis is not checked

The data points for the line show up as (1.6,0) and (1.6,200) as you would expect. The X axis for the line series goes from 0 to 2.0 as if I had selected a secondary axis.

 

[Mark W.]

On 2002-02-26 12:41, rdionne wrote:
1) Both series have the same category axis labels: =HistChartData!$A$1:$A$31
2) Secondary axis is not checked

The data points for the line show up as (1.6,0) and (1.6,200) as you would expect. The X axis for the line series goes from 0 to 2.0 as if I had selected a secondary axis.

> The X axis for the line series goes from 0 to 2.0...

What do you mean by this?
Is the line vertical?
This message was edited by Mark W. on 2002-02-26 13:00

 

[rdionne]

Never mind. If I turn on the secondary axis the range goes between 0 and 2. (useless info). Actually the X axis range for the line goes from 0.5 to 31.5 . That's why my line gets pushed all the way to the left. I found this out by moving one of the data points of the line from far left to far right of the plot area and watching the tooltip values. I have no idea where it's getting that range from.

 

[Mark W.]

Is the line vertical?