Help, my columns are negatives! -- bug?

[Del Cotter]

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.

 

[Jon Peltier]

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

 

[Del Cotter]

Thanks for your help, Jon!

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]

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"

 

[Jon Peltier]

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]

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

 

[Del Cotter]

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/politics/chart/house_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".