Help, my columns are negatives! -- bug?
 

I'm baffled by this, has anyone else seen anything like it?

I had a mixed set of time series of congressional elections, with
vertical markers and horizontal lines thrown in, two curves, two sets of
vertical lines, and one horizontal line, all as a pure scatter chart
format, and I wanted to add a column series to provide shading bands for
presidential party in the same period, grey for one party and untouched
for the other. So I created a series of 1,0,0,1, etc. and added it to
the chart, giving it its own secondary y-axis, not because it needed one
but because it did need a second category x-axis (because all the other
series were using an interval x-axis, since they were XY points)

I had shrunk the gap between bars to zero and set the border to "none",
when I noticed that the bars were the opposite of where I expected them
to be, grey for the party I had wanted blank, and blank where I wanted
grey; so I brought the borders and gaps back, and sure enough the
columns were where there were supposed to be no columns, and vice versa.
Even more bizarre, when I brought the tops of the columns down into
view, columns started in mid air where the data should have ended! I
have columns that are photographic negatives of the data!

I haven't time to post an example chart or spreadsheet, but may later if
anyone needs it, or I can email. But has anyone encountered this, what
looks like a plain and simple bug to me?

I am using Excel 97 on Windows XP.

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

Help, my columns are negatives! -- bug?
 

Del -

The secondary category axis is at the top of the chart. The columns go from
this axis to the value, so the zero columns go from the top of the chart
down to zero, while the one columns go from the top of the chart to 1, which
is the top of the chart. This is normal.

The easiest way to deal with this is to swap the 0 and 1 values, and let the
columns hang from the top of the chart rather than grow from the bottom.

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


"Del Cotter" wrote in message
...

I'm baffled by this, has anyone else seen anything like it?

I had a mixed set of time series of congressional elections, with vertical
markers and horizontal lines thrown in, two curves, two sets of vertical
lines, and one horizontal line, all as a pure scatter chart format, and I
wanted to add a column series to provide shading bands for presidential
party in the same period, grey for one party and untouched for the other.
So I created a series of 1,0,0,1, etc. and added it to the chart, giving
it its own secondary y-axis, not because it needed one but because it did
need a second category x-axis (because all the other series were using an
interval x-axis, since they were XY points)

I had shrunk the gap between bars to zero and set the border to "none",
when I noticed that the bars were the opposite of where I expected them to
be, grey for the party I had wanted blank, and blank where I wanted grey;
so I brought the borders and gaps back, and sure enough the columns were
where there were supposed to be no columns, and vice versa. Even more
bizarre, when I brought the tops of the columns down into view, columns
started in mid air where the data should have ended! I have columns that
are photographic negatives of the data!

I haven't time to post an example chart or spreadsheet, but may later if
anyone needs it, or I can email. But has anyone encountered this, what
looks like a plain and simple bug to me?

I am using Excel 97 on Windows XP.

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

Help, my columns are negatives! -- bug?
 

Thanks for your help, Jon!

On Sun, 5 Nov 2006, in microsoft.public.excel.charting,
Jon Peltier said:

The secondary category axis is at the top of the chart. The columns go from
this axis to the value, so the zero columns go from the top of the chart
down to zero, while the one columns go from the top of the chart to 1, which
is the top of the chart. This is normal.

Oh, right, I see. Well, it may be normal for Excel, but I still think
it's a bug in Excel.

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

Help, my columns are negatives! -- bug?
 

On Sun, 5 Nov 2006, in microsoft.public.excel.charting,
Jon Peltier said:

The easiest way to deal with this is to swap the 0 and 1 values, and let the
columns hang from the top of the chart rather than grow from the bottom.

Jon, within seconds of sending off my previous reply, I found a better
solution: uncheck the box in the secondary Y axis settings that says
"Category (X) axis crosses at maximum value"

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

Help, my columns are negatives! -- bug?
 

It's no bug. It's very consistent behavior, and if you understand it, you
can make it work for you.

The column series all start at the X axis. Make a column chart with any
data, and the columns start at the X axis (Y=0) and reach upward to positive
values and downward to negative values. Lock the Y min and max, but change
the Y value where the X axis crosses. Now the columns start at the new axis
position, and move upward to X values greater than the Crosses At value and
downward to X values less that the Crosses At value.

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


"Del Cotter" wrote in message
...
Thanks for your help, Jon!

On Sun, 5 Nov 2006, in microsoft.public.excel.charting,
Jon Peltier said:

The secondary category axis is at the top of the chart. The columns go
from
this axis to the value, so the zero columns go from the top of the chart
down to zero, while the one columns go from the top of the chart to 1,
which
is the top of the chart. This is normal.

Oh, right, I see. Well, it may be normal for Excel, but I still think it's
a bug in Excel.

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

Help, my columns are negatives! -- bug?
 

That also works. I didn't know if you needed to display the secondary X
axis. Changing the setting as you did hides it behind the primary X axis, or
sometimes shows both overlapping themselves..

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


"Del Cotter" wrote in message
...
On Sun, 5 Nov 2006, in microsoft.public.excel.charting,
Jon Peltier said:

The easiest way to deal with this is to swap the 0 and 1 values, and let
the
columns hang from the top of the chart rather than grow from the bottom.

Jon, within seconds of sending off my previous reply, I found a better
solution: uncheck the box in the secondary Y axis settings that says
"Category (X) axis crosses at maximum value"

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

Help, my columns are negatives! -- bug?
 

On Mon, 6 Nov 2006, in microsoft.public.excel.charting,
Jon Peltier said:

That also works. I didn't know if you needed to display the secondary X
axis. Changing the setting as you did hides it behind the primary X axis, or
sometimes shows both overlapping themselves..

I didn't exactly need to, but it was nice to have tick marks top and
bottom. As it happens, I can still have that, by checking "X axis
crosses at maximum value" for the primary axis. When I thought I had no
other alternative, I was going to rebuild the whole graph starting from
the bars, and putting the scatter ranges on after, but this is less
hassle :-) And here is the finished product:

http://www.branta.demon.co.uk/politi..._balance_2.png

Note, as a final twist, the labels on the bottom are actually the
primary X axis labels, even though the primary X axis is at the top and
the secondary at the bottom--I set the labels to "Low" instead of "Next
to axis".

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