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

Answers

  • 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)


    Saturday, February 8, 2014 4:27 PM

All replies

  • 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)


    Saturday, February 8, 2014 4:27 PM
  • Thank you very, very much, Hans!!!
    Mike Zhang
    Sunday, February 9, 2014 3:00 AM