Hello Mr Excel members ...
A long time ago, in 2011, a member of the forum posted a thread
as to how to compute 'major unit' in excel-charts.
The post had the title 'The Mystery Major Unit' and a search
in the forum confirmed that it was still there, though the member
seeming hasn't posted any new threads since 2014 ...
He and others were troubled as they could not figure how
Excel calculated the major units shown in charts.
He found out correctly that there were some 'magical' numbers,
when a spreadsheets tried to calculate the spacing between the
major units ...
So lets see just how this plays out ...
In the following, I will only be dealing with the special XY-point charts
where you can plot a set of xy-data points ...
Say we have the follwing series:
x: 1, 2, 3, ..., N (N values)
y: y_1, y_2, ... y_N (N values)
where the y-values can be all positive, all negative or both
positive and negative ...
One thing to notice though, is the value of r = min(|y_i|)/max(|y_i|)
in the special cases where the y-series is either all positive or all
negative. In these cases, if r < 5/6, the y-axis starts off at zero,
and the method of finding the major units in the following
will not work. For example, if
y: -232.3, -189.7, -204.5 (r = 0.8166)
or
y: 0.453, 0.632, 0,854 (r = 0.5304)
then the y-axis start at zero. Therefore, in the follwing, I shall assume that
if the y-series are either all positive or all negative, the r-value is greater
than 5/6.
For simplicity, lets take our y-series as
y: 1.323, 1.276, 1.365, 1.476, 1.433 (N = 5)
Here min(|y_i|) = 1.276, max(|y_i|) = 1.476, so r = 0.8645 > 5/6
My goal here is to predict how an XY-chart will render these data, i.e
what's the spacing between major units, the maximum and minimum major units
and how many values are shown along the y-axis.
To this end, lets start by defining what someone on another forum has called
the automatic minimum and automatic maximum:
aut_min = min(y_i) - (max(y_i) - min(y_i))/20 (automatic minimum)
aut_max = max(y_i) + (max(y_i) - min(y_i))/20 (automatic maximum)
So with the given y-series, aut_min = 1.266, aut_max = 1.486.
Now compute what I call an automatic difference or aut_diff:
aut_diff = aut_max - aut_min
Here, aut_diff = 0.22. It turns out that this automatic difference is crucial
in finding the spacing between the major units ...
Now convert the automatic difference to a number in the open interval from 1 to 10
by multiplying or dividing by a natural power of ten, in my case by multiplying by
one power of ten:
key_number = 0.22 x 10 = 2.2
In general how do you convert the automatic difference to a number in the open
interval from 1 to 10?
First, take the log with base 10 of the automatic diffenrence:
LOG10(aut_diff)
Next, round down to nearest integer:
FLOOR(LOG10(aut_diff), 1)
Next, raise ten to this power:
10^FLOOR(LOG10(aut_diff), 1)
Finally, divide automatic difference by your result so far:
aut_diff / 10^FLOOR(LOG10(aut_diff), 1) (the key_number)
This gives the required converted number in the open interval from 1 to 10.
Now to the 'magical numbers' as stated by the member in his post 'The Mystery Major Unit'
back in 2011:
Based on the value of the key number, the 'magical number' are found:
If key_number >= 5, pick 10, else if key_number >= 2, pick 5, else pick 2.
What's found is what I call the relative increment, though not the absolute increment
that detemines the spacing between major units.
So in the y-series given, with key_number = 2.2, the relative increment is 5:
inc_rel = 5 (relative increment)
Clearly, with the y-series ranging from 1.276 to 1.476, the number 5 is not
the spacing between major units, but we're almost there ...
For some reason the Excel team has chosen to render approximately 10 values
along the y-axis, ie there's always 6, 7, ... 12 shown numbers along it.
The spacing between major units is now some integer power of ten times the relative
increment such that there's about 10 shown values along the y-axis: take the
previously found power of ten and multiply by the relative increment and divide
by ten:
inc_abs = inc_rel x 10^FLOOR(LOG10(aut_diff), 1) / 10
or in my example, inc_abs = 0.05. That gives us the spacing between major units.
Finally, we're in position to determine upper and lower major units:
upper_major_unit = CEILING(aut_max, inc_abs)
lower_major_unit = FLOOR(aut_min, inc_abs)
So how many values are shown along the y-axis?
We get:
#values = 1 + (upper_major_unit - lower_major_unit) / inc_abs
finding #values = 6 in with this example.
Try it out with your own data. It works perfectly in Office 2010 ...
A long time ago, in 2011, a member of the forum posted a thread
as to how to compute 'major unit' in excel-charts.
The post had the title 'The Mystery Major Unit' and a search
in the forum confirmed that it was still there, though the member
seeming hasn't posted any new threads since 2014 ...
He and others were troubled as they could not figure how
Excel calculated the major units shown in charts.
He found out correctly that there were some 'magical' numbers,
when a spreadsheets tried to calculate the spacing between the
major units ...
So lets see just how this plays out ...
In the following, I will only be dealing with the special XY-point charts
where you can plot a set of xy-data points ...
Say we have the follwing series:
x: 1, 2, 3, ..., N (N values)
y: y_1, y_2, ... y_N (N values)
where the y-values can be all positive, all negative or both
positive and negative ...
One thing to notice though, is the value of r = min(|y_i|)/max(|y_i|)
in the special cases where the y-series is either all positive or all
negative. In these cases, if r < 5/6, the y-axis starts off at zero,
and the method of finding the major units in the following
will not work. For example, if
y: -232.3, -189.7, -204.5 (r = 0.8166)
or
y: 0.453, 0.632, 0,854 (r = 0.5304)
then the y-axis start at zero. Therefore, in the follwing, I shall assume that
if the y-series are either all positive or all negative, the r-value is greater
than 5/6.
For simplicity, lets take our y-series as
y: 1.323, 1.276, 1.365, 1.476, 1.433 (N = 5)
Here min(|y_i|) = 1.276, max(|y_i|) = 1.476, so r = 0.8645 > 5/6
My goal here is to predict how an XY-chart will render these data, i.e
what's the spacing between major units, the maximum and minimum major units
and how many values are shown along the y-axis.
To this end, lets start by defining what someone on another forum has called
the automatic minimum and automatic maximum:
aut_min = min(y_i) - (max(y_i) - min(y_i))/20 (automatic minimum)
aut_max = max(y_i) + (max(y_i) - min(y_i))/20 (automatic maximum)
So with the given y-series, aut_min = 1.266, aut_max = 1.486.
Now compute what I call an automatic difference or aut_diff:
aut_diff = aut_max - aut_min
Here, aut_diff = 0.22. It turns out that this automatic difference is crucial
in finding the spacing between the major units ...
Now convert the automatic difference to a number in the open interval from 1 to 10
by multiplying or dividing by a natural power of ten, in my case by multiplying by
one power of ten:
key_number = 0.22 x 10 = 2.2
In general how do you convert the automatic difference to a number in the open
interval from 1 to 10?
First, take the log with base 10 of the automatic diffenrence:
LOG10(aut_diff)
Next, round down to nearest integer:
FLOOR(LOG10(aut_diff), 1)
Next, raise ten to this power:
10^FLOOR(LOG10(aut_diff), 1)
Finally, divide automatic difference by your result so far:
aut_diff / 10^FLOOR(LOG10(aut_diff), 1) (the key_number)
This gives the required converted number in the open interval from 1 to 10.
Now to the 'magical numbers' as stated by the member in his post 'The Mystery Major Unit'
back in 2011:
Based on the value of the key number, the 'magical number' are found:
If key_number >= 5, pick 10, else if key_number >= 2, pick 5, else pick 2.
What's found is what I call the relative increment, though not the absolute increment
that detemines the spacing between major units.
So in the y-series given, with key_number = 2.2, the relative increment is 5:
inc_rel = 5 (relative increment)
Clearly, with the y-series ranging from 1.276 to 1.476, the number 5 is not
the spacing between major units, but we're almost there ...
For some reason the Excel team has chosen to render approximately 10 values
along the y-axis, ie there's always 6, 7, ... 12 shown numbers along it.
The spacing between major units is now some integer power of ten times the relative
increment such that there's about 10 shown values along the y-axis: take the
previously found power of ten and multiply by the relative increment and divide
by ten:
inc_abs = inc_rel x 10^FLOOR(LOG10(aut_diff), 1) / 10
or in my example, inc_abs = 0.05. That gives us the spacing between major units.
Finally, we're in position to determine upper and lower major units:
upper_major_unit = CEILING(aut_max, inc_abs)
lower_major_unit = FLOOR(aut_min, inc_abs)
So how many values are shown along the y-axis?
We get:
#values = 1 + (upper_major_unit - lower_major_unit) / inc_abs
finding #values = 6 in with this example.
Try it out with your own data. It works perfectly in Office 2010 ...
