Values plotted outside of Axis scale

[JoeAtADA]

I'm plotting data in a scatter plot. The data values span -1 to ~16,000 and
I want them on the Y axis. I do not want to plot the -1 values. I've set
the Y axis scale minimum value to 0, but the -1 values are still displayed.
I use the -1 values later in the chart, so replacing them with another #
might be an issue. Outside of changing the minimum value until the -1 values
disappear, is there any way to remove those values?

 

[Jon Peltier]

Do you get data points outside of the plot area? How did you set your axis
minimum? Did you set it to zero, then check the box that says Auto? Auto
means let Excel assign the parameter, so you do not want to check Auto.

- Jon

 

[JoeAtADA]

I manually set the minimum value for zero. I did not check the autoscale
box. It seems to be an issue due to the large span of the data. If I try my
setup with data over about 500, the -1 values appear. If the data contain
points under ~500, the data disappear.
Also, this is in Excel 2003.

 

[Bernard Liengme]

I have seen this behaviour before with a very large data set. It was sent to
me by a grad student years ago (I recall it had to do with birtds) and I am
trying to recall the simple solution. If it comes in my dreams, I will let
you know. Meanwhile, rest asssure it is likely not a problem with your PC or
Excel installation
If you send me the file (private email) I will have a look - but Jon is the
chart expert
best wishes

 

[Jon Peltier]

Well, that's weird. I have about 25 points, with Y values ranging between 0
and almost 5000. I set the min to 0 and the max to 5000, and inserted a
small number of negative values. The points for -1 and -5 appear on the
chart, and -10 does not. It looks like the -1 and -5 points are still
centered on the axis line, and since 10 is one pixel above, -10 must be one
pixel below and therefore is considered to be "off the chart".

If I have a chance soon I'll submit this as a bug. Since it doesn't seem to
occur in Excel 2007 (based on very limited testing), it's probably not worth
submitting.

- Jon

 

[JoeAtADA]

Thank you both. It's inconvenient, but I'll survive...

 

[Jon Peltier]

Are the data linked from somewhere else? You could change the link formula
from

=A1

to

=IF(A1<0,NA(),A1)

The NA() produces an error in the cell, but any marker-type series doesn't
plot the point.

- Jon