Hi

I am doing a line chart and would like two labels for the x axis
accompanying the same data point. The problem I am having is the formatting
of the labels. I would like to fit them in both with the same orientation
(vertical). But only one label goes vertical when I do this. There is a
column for one label (date), and another for ID (which is a #).

thanks

On Tue, 1 May 2007, in microsoft.public.excel.charting,
BillO <(E-Mail Removed)> said:
>I am doing a line chart and would like two labels for the x axis
>accompanying the same data point. The problem I am having is the formatting
>of the labels. I would like to fit them in both with the same orientation
>(vertical). But only one label goes vertical when I do this. There is a
>column for one label (date), and another for ID (which is a #).

Yes, that's a problem with having two labels on the X axis.

Are you prepared to go radical and abandon the Microsoft-provided axis
facility? If so, you can create your own customised X axis with all the
properties you want, by creating a data series, labelling it, and
formatting it to look like an axis. For two labels you either want a
specially-formatted label, or more likely two series.

Have a look at the "Arbitrary axis" examples on Jon Peltier's "Chart
Axes and Axis Tricks" page:

http://peltiertech.com/Excel/Charts/axes.html#ArbAxis

Tushar Mehta's "Flexible log scale" tutorial is about Y axes, but the
principles are the same, and it may help you if you get stuck.

http://www.tushar-mehta.com/excel/ne...ble_log_scale/

--
Del Cotter
NB Personal replies to this post will send email to (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.

Another option would be to concatenate your ID numbers and dates together
into one X-Axis range as opposed to two. For example, given your ID numbers
in column A and your dates in column B, add a formula similar to the one
below into column C:

=B1&" | "&TEXT(A1,"dd/mm/yy")

Then reference your X-Axis off of column C.

--
John Mansfield
http://cellmatrix.net





"BillO" wrote:

> Hi
>
> I am doing a line chart and would like two labels for the x axis
> accompanying the same data point. The problem I am having is the formatting
> of the labels. I would like to fit them in both with the same orientation
> (vertical). But only one label goes vertical when I do this. There is a
> column for one label (date), and another for ID (which is a #).
>
> thanks

BillO:

Del's arbitrary axis suggestion is a good one. Here a link to my how-to
tutorial on custom axes.
http://processtrends.com/pg_charts_custom_axis.htm.

I have a two label y axis example at this link.
http://processtrends.com/toc_chart_d..._Y_Axis_Labels

While it takes a little patience to master custom axes, once you do you can
start making Excel do things you never thought possible.

Kelly

http://processtrends.com






"Del Cotter" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Tue, 1 May 2007, in microsoft.public.excel.charting,
> BillO <(E-Mail Removed)> said:
>>I am doing a line chart and would like two labels for the x axis
>>accompanying the same data point. The problem I am having is the
>>formatting
>>of the labels. I would like to fit them in both with the same orientation
>>(vertical). But only one label goes vertical when I do this. There is a
>>column for one label (date), and another for ID (which is a #).
>
> Yes, that's a problem with having two labels on the X axis.
>
> Are you prepared to go radical and abandon the Microsoft-provided axis
> facility? If so, you can create your own customised X axis with all the
> properties you want, by creating a data series, labelling it, and
> formatting it to look like an axis. For two labels you either want a
> specially-formatted label, or more likely two series.
>
> Have a look at the "Arbitrary axis" examples on Jon Peltier's "Chart Axes
> and Axis Tricks" page:
>
> http://peltiertech.com/Excel/Charts/axes.html#ArbAxis
>
> Tushar Mehta's "Flexible log scale" tutorial is about Y axes, but the
> principles are the same, and it may help you if you get stuck.
>
> http://www.tushar-mehta.com/excel/ne...ble_log_scale/
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.

On Tue, 1 May 2007, in microsoft.public.excel.charting,
Kelly O'Day <(E-Mail Removed)> said:

>While it takes a little patience to master custom axes, once you do you can
>start making Excel do things you never thought possible.

If I was designing a charting module for spreadsheets today, I would
make axes be just another kind of data series, with marker types
available for correct appearance and a chart wizard for worry-free setup
by non-expert users.

This actually mirrors the way I was taught graphing as a child at
school: the "x-axis" was always referred as the line obeying the
equation y=0, and the "y axis" was always called x=0. The idea was to
encourage children to understand that there is nothing special or magic
about those parts of graph space, and especially nothing particularly
magic about the origin point (0,0).

(although it mainly confused and annoyed me at the time because I got
the language mixed up: was x=0 the x axis? :-)

I have had colleagues freak out when they see me do axes that aren't on
the zero line, especially when I do the custom axis trick of not having
the two axes joined at one corner, but each floating free with a gap of
white space. They understand better when I call them "scale bars" and
compare them to the scale bars on a map, with the graph space analogous
to a map area.

--
Del Cotter
NB Personal replies to this post will send email to (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.

Del -

I guess my own idea of "axes" intuitively incorporates your "scale bars",
which is why I've been able to easily incorporate all kinds of axis effects
in my work. (Also I learned computer charting on an HP plotter, using HPGL
commands to move a pen around the page. I had to do all my own
determinations of scales, tick and label location, etc.) Other people aren't
so intuitive with this, which is why it sometimes takes an hour or a dozen
emails to get the idea across.

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


"Del Cotter" <(E-Mail Removed)> wrote in message
news:2ViQw7AJ3DOGFwE$@branta.demon.co.uk...
> On Tue, 1 May 2007, in microsoft.public.excel.charting,
> Kelly O'Day <(E-Mail Removed)> said:
>
>>While it takes a little patience to master custom axes, once you do you
>>can
>>start making Excel do things you never thought possible.
>
> If I was designing a charting module for spreadsheets today, I would make
> axes be just another kind of data series, with marker types available for
> correct appearance and a chart wizard for worry-free setup by non-expert
> users.
>
> This actually mirrors the way I was taught graphing as a child at school:
> the "x-axis" was always referred as the line obeying the equation y=0, and
> the "y axis" was always called x=0. The idea was to encourage children to
> understand that there is nothing special or magic about those parts of
> graph space, and especially nothing particularly magic about the origin
> point (0,0).
>
> (although it mainly confused and annoyed me at the time because I got the
> language mixed up: was x=0 the x axis? :-)
>
> I have had colleagues freak out when they see me do axes that aren't on
> the zero line, especially when I do the custom axis trick of not having
> the two axes joined at one corner, but each floating free with a gap of
> white space. They understand better when I call them "scale bars" and
> compare them to the scale bars on a map, with the graph space analogous to
> a map area.
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.

Del:

We think alike.

If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart -
Line Chart terminology. To me, all 2 D charts are XY charts; scatter and
line charts are just special forms of XY charts. How many questions show up
on the Chart forum because users want to have a "line" chart with numeric
values for X and Y.

....Kelly




"Del Cotter" <(E-Mail Removed)> wrote in message
news:2ViQw7AJ3DOGFwE$@branta.demon.co.uk...
> On Tue, 1 May 2007, in microsoft.public.excel.charting,
> Kelly O'Day <(E-Mail Removed)> said:
>
>>While it takes a little patience to master custom axes, once you do you
>>can
>>start making Excel do things you never thought possible.
>
> If I was designing a charting module for spreadsheets today, I would make
> axes be just another kind of data series, with marker types available for
> correct appearance and a chart wizard for worry-free setup by non-expert
> users.
>
> This actually mirrors the way I was taught graphing as a child at school:
> the "x-axis" was always referred as the line obeying the equation y=0, and
> the "y axis" was always called x=0. The idea was to encourage children to
> understand that there is nothing special or magic about those parts of
> graph space, and especially nothing particularly magic about the origin
> point (0,0).
>
> (although it mainly confused and annoyed me at the time because I got the
> language mixed up: was x=0 the x axis? :-)
>
> I have had colleagues freak out when they see me do axes that aren't on
> the zero line, especially when I do the custom axis trick of not having
> the two axes joined at one corner, but each floating free with a gap of
> white space. They understand better when I call them "scale bars" and
> compare them to the scale bars on a map, with the graph space analogous to
> a map area.
>
> --
> Del Cotter
> NB Personal replies to this post will send email to
> (E-Mail Removed),
> which goes to a spam folder-- please send your email to del3 instead.

I've made this suggestion, along with a more powerful bunch of OM around the
Axis object, but it would entail too large a revision to the charting
engine. Unfortunately 2007 has gone in the other direction. I have a
tutorial on my web site showing how to use line and XY series in conjunction
to get a nice stock chart with ticks for open and close instead of the
candlestick:

http://peltiertech.com/Excel/ChartsH...artTricks.html

This technique works nicely in Excel versions 2003, 2002, 2000, 97, and
probably earlier. However, it falls down in 2007, because an XY series
cannot coexist with a line series on the same axis. You would have to use
the secondary axes for the XY chart, which makes it unavailable for other
effects you could add to such a chart.

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


"Kelly O'Day" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Del:
>
> We think alike.
>
> If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart -
> Line Chart terminology. To me, all 2 D charts are XY charts; scatter and
> line charts are just special forms of XY charts. How many questions show
> up on the Chart forum because users want to have a "line" chart with
> numeric values for X and Y.
>
> ...Kelly
>
>
>
>
> "Del Cotter" <(E-Mail Removed)> wrote in message
> news:2ViQw7AJ3DOGFwE$@branta.demon.co.uk...
>> On Tue, 1 May 2007, in microsoft.public.excel.charting,
>> Kelly O'Day <(E-Mail Removed)> said:
>>
>>>While it takes a little patience to master custom axes, once you do you
>>>can
>>>start making Excel do things you never thought possible.
>>
>> If I was designing a charting module for spreadsheets today, I would make
>> axes be just another kind of data series, with marker types available for
>> correct appearance and a chart wizard for worry-free setup by non-expert
>> users.
>>
>> This actually mirrors the way I was taught graphing as a child at school:
>> the "x-axis" was always referred as the line obeying the equation y=0,
>> and the "y axis" was always called x=0. The idea was to encourage
>> children to understand that there is nothing special or magic about those
>> parts of graph space, and especially nothing particularly magic about the
>> origin point (0,0).
>>
>> (although it mainly confused and annoyed me at the time because I got the
>> language mixed up: was x=0 the x axis? :-)
>>
>> I have had colleagues freak out when they see me do axes that aren't on
>> the zero line, especially when I do the custom axis trick of not having
>> the two axes joined at one corner, but each floating free with a gap of
>> white space. They understand better when I call them "scale bars" and
>> compare them to the scale bars on a map, with the graph space analogous
>> to a map area.
>>
>> --
>> Del Cotter
>> NB Personal replies to this post will send email to
>> (E-Mail Removed),
>> which goes to a spam folder-- please send your email to del3 instead.
>
>

On Wed, 2 May 2007, in microsoft.public.excel.charting,
Kelly O'Day <(E-Mail Removed)> said:
>If I were redoing Excel's charting tool, I'd drop the XY (Scatter) Chart -
>Line Chart terminology. To me, all 2 D charts are XY charts; scatter and
>line charts are just special forms of XY charts. How many questions show up
>on the Chart forum because users want to have a "line" chart with numeric
>values for X and Y.

Yes, there is an important difference between the Scatter chart and the
Line chart, but it's a difference whose value should not be held in the
"Chart type" field, but in the "Axis type" field. The Format Axis
dialogue box should offer the types "Nominal", "Ordinal", and "Interval"
as named by S. S. Stevens in the 1940s.

"Nominal" is like Excel's "Category"

"Ordinal" is like Excel's "Time-scale" (and therein lies an interesting
bug/feature/property that's the key to a favourite trick of mine for
quick and dirty step charts)

"Interval" is the scale type Excel calls "Category" when the chart type
is Scatter. But the chart type is an inappropriate way of controlling
the difference, a bad early design choice by Microsoft that's now frozen
in.

--
Del Cotter
NB Personal replies to this post will send email to (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.

On Wed, 2 May 2007, in microsoft.public.excel.charting,
Del Cotter <(E-Mail Removed)> said:
>as named by S. S. Stevens in the 1940s.
>
>"Nominal" is like Excel's "Category"
>
>"Ordinal" is like Excel's "Time-scale"
>
>"Interval" is the scale type Excel calls "Category" when the chart type
>is Scatter.

Oh, and I forgot to note that in Excel today, the Y axis is always an
interval scale; nominal and ordinal are not offered at all. Which leads
to all those kludges involving horizontal bar chart combinations or
custom-labelled dummy series, to provide labels up the left side. In my
dream Turbo Excel Charts, the X axis would offer N, O, and I types, *and
so would the Y*.

P.S. But like I said, in my dream software there would be no axes
really, in the sense of there being something different from data
series.

P.P.S. Any minute now, some smart alec is going to recommend R :-)

--
Del Cotter
NB Personal replies to this post will send email to (E-Mail Removed),
which goes to a spam folder-- please send your email to del3 instead.