Producing line and bar graphs on same axis

S

smurray444

Dear all,

I have two datasets in Excel 2002 showing glacial recession for a
particular glacier in Switzerland. The column headings are the same for
both datasets, and are as follows: Timespan, No. of years, Glacier
Length Change (m), Cumulative Length Change (m), and Change Rate Per
Year (m/year).

I've been trying to graph Length Change (as bars) and Cumulative Length
Change (as lines) on the same axes, using the Line/Column (with 2 axes)
option under the custom graphs section of the chart wizard. I'm able to
produce bars and lines for each respective dataset (just to clarify,
there should be two bars adjacent to each other for each year, and two
lines on the graph), but I'm finding that the data-points for the
lines, and the bars do not correlate with the years... they appear to
lose synch with each other.

I think this is for three reasons:

1. Due to the time-span format of the years, and the fact that one
dataset begins in 1811, whereas the other starts in 1856 - I'm finding
though, that rather than one dataset starting further along the x-axis
(years), that Excel is starting both on the far left of this axis, and
not recognising the years as years but rather simply as integers.

2. The timespans are of unequal intervals (hence the 'no. of years'
column) - for example, the timespans in the first dataset are:

1811-1822
1822-1835
1835-1857
1857-1865
1865-1870
1870-1885
1885-2005


Whereas in the second they begin:
1856-1886
1886-1887
1887-1889
1891-1892
1892-1893
1893-1894
1894-1895
1895-1896
1896-1897
1897-1898
... upto 2004 - so whilst the first timespan is of 30 years, the rest
are mainly 1 (yet see line 3), and there is some similar slight
varience later on in this dataset.

3. The data isn't fully continuous in the second dataset - see lines 3
to 4 in the list above, where there is no data for 1889-1891. I
wouldn't think this should be a problem, as I'd be able to interpolate
the gaps, but Excel seems to not to leave a gap, leading to the
subsequent data coming out of synch with their respective years.

To clarify further, the second datset I have is taken from the
following website - the graph should hopefully look like the one shown
there, just with my own data included too (which calls for a different
timescale): http://glaciology.ethz.ch/messnetz/glaciers/arolla.html

I have also included an attachment of the data.

I hope this is clear - if not please feel free to e-mail me at
(e-mail address removed)

Thanks very much for your time and efforts,
Steve Murray


+-------------------------------------------------------------------+
|Filename: GlacierRecessionData.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4051 |
+-------------------------------------------------------------------+
 
J

Jon Peltier

If you're using the Line/Column chart type, then please refer to the chart
elements as Columns. Otherwise your description is confusing. I know we were
brought up calling them bars whether vertically or horizontally oriented,
but Excel makes the distinction. I'm sorry if this sounds overly retentive,
but combining bars with lines is a much different chore than combining
columns with lines.

Now then, you should understand how a category axis works. The "values" are
interpreted by Excel as having no intrinsic numerical value, but are simply
placeholder labels. Excel starts with the first placeholder, then puts the
second one next to the first, and so on until it runs out of placeholders.
You'll note that your placeholders do not line up:
1811-1822 1856-1886
1822-1835 1886-1887
1835-1857 1887-1889
etc.
Click to expand...

You could insert blank placeholders into each set of data, until each set
has exactly the same placeholders. The unequal time periods will cause
problems.

Or you could rethink your charting approach. Here's how I would produce the
chart on the site you cited:

First, this would be a purely XY chart, no bars or columns or whatever.

Series 1, XY, but formatted with no lines and no markers (*invisible* but
don't make it invisible quite yet).
X = year, not span of years, but a single numerical value for the year. Use
the latest year of the span, or divide the multi-year length change equally
among the years in the span.
Y = length change.

The appearance of columns will be produced using error bars. We need two
columns for error bar values, one for upward and one for downward error
bars. In the upward column use a formula like =max(0,B2), where B2 is the Y
value. In the downward column, use a formula like =min(0,B2). Fill these
formulas down as far as you need to.

When you've plotted the series and prepared the error bar data, double click
on the series (obviously you don't make it invisible until after this step).
On the Y Error Bars tab, click in the Custom Positive box, and select the
range with the downward error bars (yes, it seems backwards, but it's not),
then click in the Custom Negative box, and select the range with the upward
error bars. Now you can hide the markers. Double click the Upward error
bars, and format the error bars to be a think line of the desired color,
without the end caps. Do the same for the other error bars.

Series 2, XY with Markers (like the black circles on the site).
X = year, the exact same range of values as for series 1
Y = cumulative length change

Series 2 is plotted on the secondary axis.

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