Producing line and bar graphs on same axis

[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 hope this is clear - if not please feel free to e-mail me at smurray444@hotmail.com

Thanks very much for your time and efforts,
Steve Murray

 

[GlennUK]

Looking at the graph on that website, I'd say that the data was expanded to one point per year, as the length change figures have been converted from amount per reading to amount per year, and plotted as such. Think about converting your data to a point per year ( a value per year ).