Number of labels on X-axis one more than number of values on Y-axi

I am making a graph with columns, where each column represents water use on a
certain time interval. I have 38 time intervals which are, each one, defined
by an initial and final date, such that the final date for one column is
always the initial date for the next column.

I have the dates on the category (X) axis, and they have to be exact. (E.g.
the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
There is never the same number of days between two dates so the only way I
see to keep the exact dates is to define the axis as a category axis (not
“Time-scale”).

So here is my problem:
The number of dates is one more (39) than the number of columns, and I want
the first date to be right below a tick mark at the LEFT side of the first
column and so on. Then I want the last date to be right below a tick mark at
the RIGHT side of the very last column.

The tick marks are at the right place BUT, the first date appears right
below the middle of the first column and so on, and the last date is always
missing!


Does anyone know a way to fix this or is it maybe impossible to have the
number of labels one more than the number of values? !!

one way to do this is to sort of cheat.

add a series of 0's corresponding to the number of labels you wish to have.
plot the two data series and labels as stacked columns
select the water series on the graph and change the axis to secondary.
after selecting the "0" series in the legend and clearing all. this should
give you what you want.
"Gudrun" wrote:

> I am making a graph with columns, where each column represents water use on a
> certain time interval. I have 38 time intervals which are, each one, defined
> by an initial and final date, such that the final date for one column is
> always the initial date for the next column.
>
> I have the dates on the category (X) axis, and they have to be exact. (E.g.
> the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
> There is never the same number of days between two dates so the only way I
> see to keep the exact dates is to define the axis as a category axis (not
> ā€œTime-scaleā€).
>
> So here is my problem:
> The number of dates is one more (39) than the number of columns, and I want
> the first date to be right below a tick mark at the LEFT side of the first
> column and so on. Then I want the last date to be right below a tick mark at
> the RIGHT side of the very last column.
>
> The tick marks are at the right place BUT, the first date appears right
> below the middle of the first column and so on, and the last date is always
> missing!
>
>
> Does anyone know a way to fix this or is it maybe impossible to have the
> number of labels one more than the number of values? !!
>
>
>

Thank you bj

I tried this and it helped with making the final date appear too, but the
dates are still at the wrong place!

The dates are below the middle of each column but I want them to be at the
ends of the columns.... know what I mean?

The last date corresponds to a gap and that doesnĀ“t look too good



"bj" wrote:

> one way to do this is to sort of cheat.
>
> add a series of 0's corresponding to the number of labels you wish to have.
> plot the two data series and labels as stacked columns
> select the water series on the graph and change the axis to secondary.
> after selecting the "0" series in the legend and clearing all. this should
> give you what you want.



> "Gudrun" wrote:
>
> > I am making a graph with columns, where each column represents water use on a
> > certain time interval. I have 38 time intervals which are, each one, defined
> > by an initial and final date, such that the final date for one column is
> > always the initial date for the next column.
> >
> > I have the dates on the category (X) axis, and they have to be exact. (E.g.
> > the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
> > There is never the same number of days between two dates so the only way I
> > see to keep the exact dates is to define the axis as a category axis (not
> > ā€œTime-scaleā€).
> >
> > So here is my problem:
> > The number of dates is one more (39) than the number of columns, and I want
> > the first date to be right below a tick mark at the LEFT side of the first
> > column and so on. Then I want the last date to be right below a tick mark at
> > the RIGHT side of the very last column.
> >
> > The tick marks are at the right place BUT, the first date appears right
> > below the middle of the first column and so on, and the last date is always
> > missing!
> >
> >
> > Does anyone know a way to fix this or is it maybe impossible to have the
> > number of labels one more than the number of values? !!
> >
> >
> >

duh, I forgot two steps.
Goto <chart><Chart options><axis> select secondary X axis
on the chart there will be some numberrs at the top of the chart. Select
the secondary axis and on patteren select none for tick lables.

"Gudrun" wrote:

> Thank you bj
>
> I tried this and it helped with making the final date appear too, but the
> dates are still at the wrong place!
>
> The dates are below the middle of each column but I want them to be at the
> ends of the columns.... know what I mean?
>
> The last date corresponds to a gap and that doesnĀ“t look too good
>
>
>
> "bj" wrote:
>
> > one way to do this is to sort of cheat.
> >
> > add a series of 0's corresponding to the number of labels you wish to have.
> > plot the two data series and labels as stacked columns
> > select the water series on the graph and change the axis to secondary.
> > after selecting the "0" series in the legend and clearing all. this should
> > give you what you want.
>
>
>
> > "Gudrun" wrote:
> >
> > > I am making a graph with columns, where each column represents water use on a
> > > certain time interval. I have 38 time intervals which are, each one, defined
> > > by an initial and final date, such that the final date for one column is
> > > always the initial date for the next column.
> > >
> > > I have the dates on the category (X) axis, and they have to be exact. (E.g.
> > > the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
> > > There is never the same number of days between two dates so the only way I
> > > see to keep the exact dates is to define the axis as a category axis (not
> > > ā€œTime-scaleā€).
> > >
> > > So here is my problem:
> > > The number of dates is one more (39) than the number of columns, and I want
> > > the first date to be right below a tick mark at the LEFT side of the first
> > > column and so on. Then I want the last date to be right below a tick mark at
> > > the RIGHT side of the very last column.
> > >
> > > The tick marks are at the right place BUT, the first date appears right
> > > below the middle of the first column and so on, and the last date is always
> > > missing!
> > >
> > >
> > > Does anyone know a way to fix this or is it maybe impossible to have the
> > > number of labels one more than the number of values? !!
> > >
> > >
> > >

one additional step is to change the data series for the water usage to not
include the final blank cell associated with the last date.

( it is a lot easier to do than it it is to remember all of the steps to
tell someone else)

"Gudrun" wrote:

> Thank you bj
>
> I tried this and it helped with making the final date appear too, but the
> dates are still at the wrong place!
>
> The dates are below the middle of each column but I want them to be at the
> ends of the columns.... know what I mean?
>
> The last date corresponds to a gap and that doesnĀ“t look too good
>
>
>
> "bj" wrote:
>
> > one way to do this is to sort of cheat.
> >
> > add a series of 0's corresponding to the number of labels you wish to have.
> > plot the two data series and labels as stacked columns
> > select the water series on the graph and change the axis to secondary.
> > after selecting the "0" series in the legend and clearing all. this should
> > give you what you want.
>
>
>
> > "Gudrun" wrote:
> >
> > > I am making a graph with columns, where each column represents water use on a
> > > certain time interval. I have 38 time intervals which are, each one, defined
> > > by an initial and final date, such that the final date for one column is
> > > always the initial date for the next column.
> > >
> > > I have the dates on the category (X) axis, and they have to be exact. (E.g.
> > > the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
> > > There is never the same number of days between two dates so the only way I
> > > see to keep the exact dates is to define the axis as a category axis (not
> > > ā€œTime-scaleā€).
> > >
> > > So here is my problem:
> > > The number of dates is one more (39) than the number of columns, and I want
> > > the first date to be right below a tick mark at the LEFT side of the first
> > > column and so on. Then I want the last date to be right below a tick mark at
> > > the RIGHT side of the very last column.
> > >
> > > The tick marks are at the right place BUT, the first date appears right
> > > below the middle of the first column and so on, and the last date is always
> > > missing!
> > >
> > >
> > > Does anyone know a way to fix this or is it maybe impossible to have the
> > > number of labels one more than the number of values? !!
> > >
> > >
> > >

Insert a blank cell at the top of the column values, so this blank is
associated with the first date. This moves the rest of the columns one
slot to the right, lining up with dates the way you want.

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


Gudrun wrote:

> I am making a graph with columns, where each column represents water use on a
> certain time interval. I have 38 time intervals which are, each one, defined
> by an initial and final date, such that the final date for one column is
> always the initial date for the next column.
>
> I have the dates on the category (X) axis, and they have to be exact. (E.g.
> the first date is 24.1.1966, the next is 8.2.1967 and so on until 6.1.2004).
> There is never the same number of days between two dates so the only way I
> see to keep the exact dates is to define the axis as a category axis (not
> ā€œTime-scaleā€).
>
> So here is my problem:
> The number of dates is one more (39) than the number of columns, and I want
> the first date to be right below a tick mark at the LEFT side of the first
> column and so on. Then I want the last date to be right below a tick mark at
> the RIGHT side of the very last column.
>
> The tick marks are at the right place BUT, the first date appears right
> below the middle of the first column and so on, and the last date is always
> missing!
>
>
> Does anyone know a way to fix this or is it maybe impossible to have the
> number of labels one more than the number of values? !!
>
>
>