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


--
afsarul
------------------------------------------------------------------------
afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
View this thread: http://www.excelforum.com/showthread...hreadid=378166

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


--
afsarul
------------------------------------------------------------------------
afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
View this thread: http://www.excelforum.com/showthread...hreadid=378166

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.
ChartSource dataSeries

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.


--
afsarul
------------------------------------------------------------------------
afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
View this thread: http://www.excelforum.com/showthread...hreadid=378166

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


--
afsarul
------------------------------------------------------------------------
afsarul's Profile: http://www.excelforum.com/member.php...o&userid=24203
View this thread: http://www.excelforum.com/showthread...hreadid=378166