Uniform Year Axis in Excel

Hi,

I'm currently in the middle of (what I believe to be ) my world-shattering dissertation on when world oil will peak. But I'm stuck on the simplest of issues. I have graphes (300+) which on the y-axis display production/reserves ranges for different countries and on the x-axis display a year range from pre 1900 to 2025. But the year range is not uniform e.g Pre -, 1904, 1909, 1914 ....... 2024. I mean it is actually uniform i.e in steps of 5, but I would like the axis to read Pre -, 1900, 1905, 1910, ...... 2025. I've tried fiddling around by change from line to scatter graphes, or by changing the number of catagories between tick mark labels, but to no avail. The problem (as I see it) lies with the 'Pre-' label which is at the beginning of the range. But I don't know what to do. Even my University can't seem to help. Please, any help would be much appreciated. My supervisor is super-excited to publish my information so that he can send a political message to the world about the impending world oil shortage.

Kind Regards,

Saad

your best bet is to use 1900 for the the Pre 1900 time frame
plot the graaph
then add a text cell appropriately formated with Pre just in front of the 1900

"afsarul" wrote:

>
> Hi,
>
> I'm currently in the middle of (what I believe to be ) my
> world-shattering dissertation on when world oil will peak. But I'm
> stuck on the simplest of issues. I have graphes (300+) which on the
> y-axis display production/reserves ranges for different countries and
> on the x-axis display a year range from pre 1900 to 2025. But the year
> range is not uniform e.g Pre -, 1904, 1909, 1914 ....... 2024. I mean
> it is actually uniform i.e in steps of 5, but I would like the axis to
> read Pre -, 1900, 1905, 1910, ...... 2025. I've tried fiddling around
> by change from line to scatter graphes, or by changing the number of
> catagories between tick mark labels, but to no avail. The problem (as I
> see it) lies with the 'Pre-' label which is at the beginning of the
> range. But I don't know what to do. Even my University can't seem to
> help. Please, any help would be much appreciated. My supervisor is
> super-excited to publish my information so that he can send a political
> message to the world about the impending world oil shortage.
>
> Kind Regards,
>
> Saad
>
>
> --
> afsarul
> ------------------------------------------------------------------------
> afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
> View this thread: http://www.excelforum.com/showthread...hreadid=378166
>
>

Thank you for the reply. I'm not sure if I understood you clearly, but the method you suggested for me would not be valid, since the 'pre-' label contains significant data that I cannot ignore.

Maybe to explain myself a bit more. I have a data range that starts at 'pre-' and ends at '2025'. The interval is yearly i.e pre-, 1900, 1901, 1902, 1903...2025 (with the exception of the 'pre-' which is data summed from years preceding 1900). I would like the x-axis to read pre-, 1900, 1905, 1910, 1915 ... 5 years ..., 2025. Instead it reads pre-, 1904, 1909, 1914, 1919 .... 5 years .... 2024. It doesn't change whether its a line or scatter and the category spacing is at 5.

Still your help is appreciated, my Masters is seemingly hanging on this trivial issue.

Regards,

Saad

with a scatter graph, you should be able to change the minimum value for
your x axis

in a line chart you should be able to set up a series saying "pre", "1900",
"1905" etc at the appropriate spacing and select them as your catagory X
axis labels.
<Chart><Source data><Series>

Aha after reading your response again you already have done this. in your
line with pre 1900 1901 etc delete the ones which do not end in 0 or 5
and select a catagory spacing of 1 if you want the same spacing between the
pre and 1900, add some cells between the pre and 1900

"afsarul" wrote:

>
> Thank you for the reply. I'm not sure if I understood you clearly, but
> the method you suggested for me would not be valid, since the 'pre-'
> label contains significant data that I cannot ignore.
>
> Maybe to explain myself a bit more. I have a data range that starts at
> 'pre-' and ends at '2025'. The interval is yearly i.e pre-, 1900, 1901,
> 1902, 1903...2025 (with the exception of the 'pre-' which is data summed
> from years preceding 1900). I would like the x-axis to read pre-, 1900,
> 1905, 1910, 1915 ... 5 years ..., 2025. Instead it reads pre-, 1904,
> 1909, 1914, 1919 .... 5 years .... 2024. It doesn't change whether its
> a line or scatter and the category spacing is at 5.
>
> Still your help is appreciated, my Masters is seemingly hanging on this
> trivial issue.
>
> Regards,
>
> Saad
>
>
> --
> afsarul
> ------------------------------------------------------------------------
> afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
> View this thread: http://www.excelforum.com/showthread...hreadid=378166
>
>

Hi, thanks again for helping. But I'm unsure as to what you mean. I can see that by deleting the cells that don't end 0 or 5, it ensures a labelling that ends with 0's and 5's. The problem being at the same time, I will be losing all those years between labels. Maybe I've misunderstood you, so an advanced 'sorry'.

An easy way of getting around the issue, is by changing the starting date after the 'pre' label to 1901 and not 1900. BUT, I can't do this, under scrutiny I'll get eaten up.

Thank you again. Please continue helping.

Saad -

Try this. Start your year data in 1895, and include 1896-1899 in the list. Put the
pre- data next to 1895, then leave 1896-1899 blank, and continue with 1900.

Make the chart (this will work with a scatter or line chart). Double click on the X
axis, select the Number tab, click on Custom in the list of categories, then in the
Type box, enter this:

[=1895]"pre-";0

What this does is display 1895 as "pre-", but still treats it as the numerical value
1895. If you're using a scatter chart, set the minimum to 1895 and the major unit to
5. If it's a line chart, set the ticks between labels to 5. With the chart still
selected, go to the Tools menu, Options, click on the Chart tab, and choose
Interpolate for Plot Empty Cells As.

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

afsarul wrote:
> Thank you for the reply. I'm not sure if I understood you clearly, but
> the method you suggested for me would not be valid, since the 'pre-'
> label contains significant data that I cannot ignore.
>
> Maybe to explain myself a bit more. I have a data range that starts at
> 'pre-' and ends at '2025'. The interval is yearly i.e pre-, 1900, 1901,
> 1902, 1903...2025 (with the exception of the 'pre-' which is data summed
> from years preceding 1900). I would like the x-axis to read pre-, 1900,
> 1905, 1910, 1915 ... 5 years ..., 2025. Instead it reads pre-, 1904,
> 1909, 1914, 1919 .... 5 years .... 2024. It doesn't change whether its
> a line or scatter and the category spacing is at 5.
>
> Still your help is appreciated, my Masters is seemingly hanging on this
> trivial issue.
>
> Regards,
>
> Saad
>
>

In a line chart you do not lose the data if you have no "label" for the X
data point all of the Y data should still be there in the graph, only the
label will not have anything If you can get the "Pre" to print on the Axis,
It will be a line chart rather than a XY chart

"afsarul" wrote:

>
> Hi, thanks again for helping. But I'm unsure as to what you mean. I can
> see that by deleting the cells that don't end 0 or 5, it ensures a
> labelling that ends with 0's and 5's. The problem being at the same
> time, I will be losing all those years between labels. Maybe I've
> misunderstood you, so an advanced 'sorry'.
>
> An easy way of getting around the issue, is by changing the starting
> date after the 'pre' label to 1901 and not 1900. BUT, I can't do this,
> under scrutiny I'll get eaten up.
>
> Thank you again. Please continue helping.
>
>
> --
> afsarul
> ------------------------------------------------------------------------
> afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
> View this thread: http://www.excelforum.com/showthread...hreadid=378166
>
>

I know this is a bit of a late reply, even so, thank you Jon and BJ. Everything did work out fine. BUT, I'm stuck again. All that time I was using a line chart for plotting, but my supervisor has asked me to switch to XY scatter. When I do this, the X-axis loses the pre-, 1900, 1905, 1910, ... 2025 year format and displays 0, 5, 10, 15 ... 140 instead. I'm sure there is a simple solution which you know, but as I understand it the 'pre-' label is the problem again. If I exclude the 'pre-' data the graph axis reverts to a 1900, 1905, 1910 etc format. Even with [=1895]"pre-";0 the axis oddity still occurs.

Thank you dearly,

Saad