This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Remove From My Forums

Excel07-10 XY(scatter) Chart y-axis default minimum and maximum problem

Question
0

Sign in to vote
Why the below chart's y-axis default value is set too far from my values, why it cannot just like the above chart's y-axis?

I mean the default axis value problem, not about how to set the fixed axis.

This is emergence problem, so, please help me, thanks!

You can find the excel file in this link:

https://skydrive.live.com/redir?resid=BB789F72272D4858%2139699

Mike Zhang
- Edited by Mike Dos Zhang Saturday, February 8, 2014 3:17 PM
Saturday, February 8, 2014 3:13 PM

Answers

1

Sign in to vote
According to http://support.microsoft.com/kb/214075 which appears to be the most recent description Microsoft has offered of the algorithm that determines the scale of chart axes, the minimum scale of the y-axis will be set to 0 if the difference between the minimum y-value (yMin) and maximum y-value (yMax) is more than 1/6 of yMax. Otherwise, the minimum scale will be the first major unit <= yMin-(yMax-yMin)/2.

In your first chart, yMin = 20 and yMax = 24, so the difference 4 is exactly 1/6 of yMax. Since it is not more than 1/6, the minimum scale is set to yMin-(yMax-YMin)/2 = 20-(24-20)/2 = 18.

Apparently the algorithm has changed slightly, since the minimum scale is not 18 but 19 in recent versions of Excel.

In the second chart, yMin = 20 and yMax = 25. The difference 5 is 1/5 of yMax. That is more than 1/6, so the minimum scale is set to 0.

Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Edited by Hans Vogelaar MVPMVP Saturday, February 8, 2014 4:29 PM
- Marked as answer by Mike Dos Zhang Sunday, February 9, 2014 3:02 AM
Saturday, February 8, 2014 4:27 PM

All replies

1

Sign in to vote
According to http://support.microsoft.com/kb/214075 which appears to be the most recent description Microsoft has offered of the algorithm that determines the scale of chart axes, the minimum scale of the y-axis will be set to 0 if the difference between the minimum y-value (yMin) and maximum y-value (yMax) is more than 1/6 of yMax. Otherwise, the minimum scale will be the first major unit <= yMin-(yMax-yMin)/2.

In your first chart, yMin = 20 and yMax = 24, so the difference 4 is exactly 1/6 of yMax. Since it is not more than 1/6, the minimum scale is set to yMin-(yMax-YMin)/2 = 20-(24-20)/2 = 18.

Apparently the algorithm has changed slightly, since the minimum scale is not 18 but 19 in recent versions of Excel.

In the second chart, yMin = 20 and yMax = 25. The difference 5 is 1/5 of yMax. That is more than 1/6, so the minimum scale is set to 0.

Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Edited by Hans Vogelaar MVPMVP Saturday, February 8, 2014 4:29 PM
- Marked as answer by Mike Dos Zhang Sunday, February 9, 2014 3:02 AM
Saturday, February 8, 2014 4:27 PM
0

Sign in to vote
Thank you very, very much, Hans!!!
Mike Zhang
- Edited by Mike Dos Zhang Sunday, February 9, 2014 3:02 AM
Sunday, February 9, 2014 3:00 AM