Floating Bars on Line-Bar Charts?

[jaygreg]

I'm using Excel 2003. I want to creat several bars on a Line-Bar chart that
begin above the X axis. Example: scale is 0 to 4000. Several lines are
plotted but a few bars are need that start at numbers like 1200 to 3500; the
bottom of the bar is at 1200 and the top is at 3500. Can someone give me a
thumbnail on how to do this or poit me to a reference please.

 

[Del Cotter]

I'm using Excel 2003. I want to creat several bars on a Line-Bar chart that
begin above the X axis. Example: scale is 0 to 4000. Several lines are
plotted but a few bars are need that start at numbers like 1200 to 3500; the
bottom of the bar is at 1200 and the top is at 3500. Can someone give me a
thumbnail on how to do this or poit me to a reference please.

Make a Stacked Column type of chart, with the first bar being 1200 and
the second bar being 3500-1200=2300. Then format the lower bar so that
it has no line and no fill. Now it's invisible, so the top bar looks as
though it's floating in air.

 

[Jon Peltier]

What Del forgot to tell you was to ignore the built-in custom chart types
and construct your own combination chart. After you get the floating columns
looking right, add another series with the line data, which adds another
column on the stack. Select the new series, go to Chart menu > Chart Type,
and select the Line chart subtype you want.

- Jon

 

[jaygreg]

Thanks guys. I'll give it a try.

What Del forgot to tell you was to ignore the built-in custom chart types
and construct your own combination chart. After you get the floating
columns looking right, add another series with the line data, which adds
another column on the stack. Select the new series, go to Chart menu >
Chart Type, and select the Line chart subtype you want.

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

 

[jaygreg]

Hummmmm. More puzzling than I anticipated. When I use the wizzard and try to
change just the columns I want to make into dtacked bars, I can't find
anything that controls just that set of data for the one year in question.
When I attempt to follow your direction, |Jon, I have trouble understanding
how I'm to proceedwithout that wizzard. I used the F11 key after reading
HELP but that was no help. I still can't control the one or two years I
want.

There are about a dozen series; two of these will be stacked columns and the
remaining 10 line type. I can't find a way to mix the types. I see two axes
charts but that still would let me stack the columns on one axes then draw
the lines on the other... or will it?

 

[jaygreg]

Spoke too soon. Found that by double-clicking a bar and selecting the entire
series, I can chage the chart typefor just that series. Problem now will by
the year 2006. There are four entries for that year but only two will be
used to create a stacked cloumn. Problem is... all four want to clime on top
of one another. Any way to just select two of the four for one year?

 

[Jon Peltier]

I'm not really clear on what you're trying to do...

You can change one series at a time, the entire series. If 'the year 2006'
indicates a series, then select the series and change its attributes. If
'the year 2006' indicates a label on the category (X) axis (i.e., one point
in each series), you can't change the series type of just one point in a
series, but you could select the series then select the point, then change
formatting of that point.

- Jon

 

[jaygreg]

Jon, here's more data:

Objective: Plot three types of medical expenses as lines on a chart having a
time from 1995 to 2006. For 2007 and beyond, these lines - when data is
available - will penetrate a stacked bar representing at the very top, the
maximum amount of "out-of pocket" charges and at the bottom of this floating
bar, the total amount paid for insurance premiums.



That year 2007 has three bars associated with it; the only bars on the chart
now that I learned how to change the others to lines. Those three bars (the
3rd represnts the out-of-pocket of a competing insuracne plan) are giving me
a headache. I can't seem to find a way to stack them... at least two of
them.

 

[Jon Peltier]

I think if you pasted your data in a reply it would be easier to see what
you need to do.

- Jon