Replies to post #65 on Learning Microsoft Excel

[Koikaze]

When charting, I have a problem because Excel arbitrarily sets the y-axis to 0 (zero) under certain circumstances. In trying to solve the problem, I found the material at the bottom of this note which I'm including in the hope it helps clarify the issue. I've highlighted the clause that causes the grief.

At present, I process the data externally, determine if the difference between yMax and yMin exceeds 16.667% of yMax. If so, I add an offset to the values. Needless to say, this is a silly way to work, but I haven't been able to find a way to suppress the highlighted rule.

Do you know any way it can be done that a layman can implement.

Thanks,

Fred


SOURCE MATERIAL:

When you create a chart in Microsoft Excel, there are three 
 possible scenarios that may apply to your data: 
  
 * The yMax and yMin values are both non-negative 
   (greater than or equal to zero). 
   This is Scenario One. 
  
 * The yMax and yMin values are both non-positive 
   (less than or equal to zero). 
   This is Scenario Two. 
  
 * The yMax value is positive, and the yMin value is negative. 
   This is Scenario Three. 
  
 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: 
 Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition 
  
 In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, 
 the following applies: 
  
 * 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.