Producing line and bar graphs on same axis

smurray444

New Member
Joined
Nov 21, 2005
Messages
48
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,685
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 ).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,624
Messages
5,832,743
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top