XL2000: How Chart Axis Limits Are Determined (214075)

MORE INFORMATION

In the following examples, the following names are used to describe certain values.

   Name   Description
   -------------------------------------------------------------------

   yMax   The maximum y value used in your chart.

   yMin   The minimum y value used in your chart.

   xMax   The maximum x value used in your chart. This applies only to
          charts that use x values, such as scatter and bubble charts.

   xMin   The minimum x value used in your chart. This applies only to
          charts that use x values, such as scatter and bubble charts.
				

When you create a chart in Microsoft Excel, there are three possible scenarios that can apply to your data:

• Scenario one: the yMax and yMin values are both positive or equal to zero.
• Scenario two: the yMax and yMin values are both negative or equal to zero.
• Scenario three: the yMax value is positive, and the yMin value is negative.

The major unit used by the y-axis is automatically determined by Microsoft Excel, based on all of the data included in the chart. The following scenarios use this default major unit.

Scenario 1

• If the chart is a 2-D area, column, bar, line or x-y scatter chart, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

  yMax + 0.05 * ( yMax - yMin )

  Otherwise, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.
• 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.
• If the difference between yMax and yMin is less than 16.667 percent of the value of yMax, the automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

  yMin - ( ( yMax - yMin ) / 2 )

  Exception: If the chart is an x-y scatter or bubble chart, the automatic minimum for the y-axis is the first major unit less than or equal to yMin.

Scenario 2

• If the chart is a 2-D area, column, bar, line or x-y scatter chart, the automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

  yMin + 0.05 * ( yMin - yMax )

  Otherwise, the automatic minimum for the y-axis is the first major unit less than or equal to yMin.
• If the difference between yMax and yMin is greater than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is zero.
• If the difference between yMax and yMin is less than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

  yMax - ( ( yMin - yMax ) / 2 )

  Exception: If the chart is an x-y scatter or bubble chart, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

Scenario 3

• The automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

  yMax + 0.05 * ( yMax - yMin )

• The automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

  yMin + 0.05 * ( yMin - yMax )

X-axis Limitations

The above information also applies to charts that use x values, such as x-y scatter charts and bubble charts. For these types of charts, substitute xMax and xMin for yMax and yMin in the above scenarios.