Scaling XY charts with suppressed starting value

[PerNielsen]

The autoscaling feature of the Excel graphing function seems to have
(limited) mind of its own. I'm trying to graph Y-values in the -1.5 t
1.5 range and X-values from 190 to 410 with the minimum from 190 to 29
and the max value from 290 to 410.

The autoscaling of Y works fine, but the X-axis always start at 0
while the high value properly sets the maximum. Of course, I could fi
the minimum at 190, but that is not what I had in mind.

Does anyone know how to overcome this limitation without using a macro

 

[Jon Peltier]

Per -

You need to make an XY Scatter chart, not a Line chart. The series can
be formatted the same, with markers and connecting lines. But in a
Scatter chart, the X axis is as easily rescaled as the Y axis.

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

 

[PerNielsen]

Jon:

Oh, if it were only that simple. As the title of my thread indicates,
AM using the XY scatter chart (almost always do because of the natur
of my data).

If you want to see the problem for yourself, make an XY chart with 36
x-values (A2..A362) and allow for entry of a starting value and a
ending value. Use NA() as an x-result if x exceedes the maximum. Ente
in A3:

=IF(A2<$D$2,A2+1,NA())

where $D$2 contains the max-x-value and A2 contains the min-x-value.

In B2, enter

=sin(A2*pi()/180)

for the y-values. Copy the formulas to row 362 and create the X
chart.

With 0 in A2 and 360 in D2, the plot is a nice sine wave.

Enter 300 in A2 and see the autoscaling work as one would expect. The
change A2 to 299 and see the autoscaling fail.

I still believe this is a bug. The wrong algorithm is use

 

[Jon Peltier]

Per -

Sorry. Your description is just like the description of someone using
the wrong type of chart for their data. 99 times out of 100, my
assumption would have been correct.

You've just discovered a known and documented behavior of Excel.
According to Knowledge Base article 101939, How Chart Axis Limits Are
Determined:

<quote>
If the difference between yMax and yMin is greater than 16.667 percent
of the value of yMax, the automatic minimum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of
the value of yMax, the automatic minimum for the y-axis is the first
major unit less than or equal to the value returned by the following
equation:

yMin - ((yMax - yMin)/2)
</quote>
(http://support.microsoft.com/default.aspx?scid=kb;[LN];101939)

Your threshold is between 300/360 (a difference of 16.667%) and 299/360
(16.944%). Whether you believe this is the wrong algorithm (and I agree:
who wants all that white space, which per the formula can be nearly
83.3% of the chart), Excel is doing what it was designed to do.
Ironically, prior to Excel 97, the lower limit was always computed the
"correct" way.

There's no way to turn off this behavior, but you can work around it.
Tushar Mehta (http://tushar-mehta.com) has an AutoChart add-in that
takes care of this, by linking the axis parameters to cells that you
specify. If you want to see the code to do this, check out this page on
my web site:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

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

 

[PerNielsen]

Jon:

Thanks a lot for the references. I tried to find help on the Microsoft
support site, but my imagination was not good enough to look for the
"right" stuff (I rarely ask for help).

My spreadsheet is used worldwide in a scientific instrument
application, so I am trying to stay away from macros and add-ins. A
customer requested modification brought this up, now I'll just set the
minimum to a fixed value and live with the limitation.

Again, thank you for your verification.

Best regards
Per Nielsen
www.pion-inc.com