Auto-scaling on Y axis inconsistent

[bebeto]

I have found that when producing XY scatter charts, the scale on the Y-axisis
not always auto-scaled to a sensible range. For example, I had a chart where
all the data points were between 195 and 250 on the Y axis. Instead of
auto-scaling the axis to run from 190 to 255 (approx) as expected, the Y axis
runs all the way down to zero, leaving a relatively large blank area on the
chart. I have tried changing the 'Format Axis' options, and ensured that
'Auto' was checked in all cases. Exponential scales are inappropriate for
this application.

In another chart however, where the data range was slightly smaller, (all
points between 210 and 250), Excel elected to sensibly auto-scale the Y axis
so that it only ran from 205 to 255, and the plot fills the chart area and is
easier to read. I would like this to happen with all my charts.

Can anyone explain what might be causing this inconsistency or suggest how i
might 'fix' the auto-scaling of axes.

Thanks

 

[Tushar Mehta]

There is a MSKB article that explains how XL sets the scaling of a chart
axis. From what I remember, it referred to XL5 or something like that but
if you are interested search msdn.microsoft.com and support.microsoft.com

As far as solving it goes, you would have to use code. Either write your
own routine that sets the axis max. and min. scales based on your own
algorithm (maybe the value in some cell?) or use a ready-made solution such
as
AutoChart Manager
http://www.tushar-mehta.com/excel/software/autochart/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[bebeto]

Thank-you for your response. I have read the MSKB article to which you refer
(214075), and it seems that XL is not behaving as randomly as I first
thought. The part that seems to be causing me problems is:

"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."

I would like to have XL effectively ignore this 'if' statement in the
algorithm, so that the axis minimum is always set to the first Major division
less than or equal to yMin. I do not have much experience with XL coding -
would anyone be able to give me some pointers as to where I might begin
editing the algorithm, if this is even possible?

 

[Tushar Mehta]

One cannot override XL's built-in algorithm though it would be nice if one
could. {grin}

The only solution that I know of is to override it. Write your own VBA
code. While the core code is trivial, generalizing it for any arbitrary
chart, and figuring out the circumstances when it should execute is what
makes it non-trivial. And, the reason for the add-in.

The trivial example. In some sheet create a chart using data in, say column
A (A1:A4 in my test). Then, in B3, which will be the 'maximum value' cell,
enter =MAX(A1:A4)+1. In C4, which will be the 'minimum value' cell, enter
=MIN(A1:A4)-1. Adjust to your own data set.

Next, get to the sheet's code module: right click the sheet's tab, then
select 'View Code'). In there enter the below code. The code changes the
max. and min. values of the y-axis for a chart so that they always equal the
value in B3 and C4 respectively.

Option Explicit
Private Sub setLimits(aChart As Chart, MaxVal As Double, _
MinVal As Double)
With aChart
.Axes(xlValue).MaximumScale = MaxVal
.Axes(xlValue).MinimumScale = MinVal
End With
End Sub

Private Sub Worksheet_Calculate()
With ActiveSheet
setLimits .ChartObjects(1).Chart, _
.Range("b3").Value, .Range("c4").Value
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Parent
If Intersect(Target, .Range("B3,C4")) Is Nothing Then Exit Sub
setLimits .ChartObjects(1).Chart, _
.Range("b3").Value, .Range("c4").Value
End With
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

The article doesn't describe how the major unit is calculated,
unfortunately. And there's no way to turn off the IF part of the minimum
determination, without turning off the automatic scaling.

- Jon