Microsoft Office Tutorials and References
In Depth Information
Value axis scales
How Excel Calculates Automatic Axis Scales
Most people use automatic axis scaling for their charts. Did you ever wonder how
Excel calculates the minimum and maximum scale values? The answer can be found at
Microsoft’s Product Support Services Web site.
It turns out that Excel uses a relatively complex algorithm. The calculation varies,
depending on the sign of the minimum and maximum values (both positive, both
negative or zero, or one is negative and the other is positive). The calculations depend
on: (1) the “major unit” specified in the Scale tab of the Format Axis dialog box; (2)
the minimum value (MIN); and (3) the maximum value (MAX).
When the values to be plotted are all positive numbers, the automatic maximum scale
value for the value axis is the first major unit that is greater than or equal to the
value returned by this expression:
MAX + 0.05 * (MAX - MIN)
Otherwise, the automatic maximum for the value axis is the first major unit greater
than or equal to the maximum value. But if the difference between the maximum and
minimum values is greater than 16.667% of the value of the maximum value, the
automatic minimum for the value axis is zero.
If the difference between the maximum and minimum values is less than 16.667% of
the maximum value, the automatic minimum for the value axis is the first major unit
that is less than or equal to the value returned by this expression:
MIN - ((MAX - MIN) / 2)
But wait! If the chart is an XY chart or a bubble chart, the automatic minimum for the
value axis is the first major unit that is less than or equal to the minimum value.
Got all that?
A category axis does not have a scale because it simply displays arbitrary
category names. For a category axis, the Scale tab of the Format Axis dialog
box displays a number of other options that determine the appearance and
layout of the axis.
The checkmarks below the Auto label indicate whether Excel calculates these
values automatically. If a setting does not have a checkmark, Excel uses the value
specified in the text box. In the example shown in Figure 4-21, all scale values are
determined automatically, except the Maximum and Major unit.
Search JabSto ::




Custom Search