Y-Axis Auto-Scale Min Erroneously Set At Zero

[Guest]

Spent all morning and half the afternoon on the forums and 'net searching for
this answer - hope I haven't overlooked it.

I have 95 duplicate data layouts I need to chart...4 different series by
month, qtr-to-date and Y-T-D. Each series is structured to contain na() if
not yet populated to serve as a placeholder for the actual data as it becomes
available. The data consist of index values which, by definition are
positive values - the most common value being 100 which is equivalent to
average.

First chart I created works perfect, deriving the y-axis from the actual
data. In particular, the y-axis runs from 85-120, with actual values ranging
from 96-115. This is important because the range will be dynamic throughout
the year. Subsequent charts (whether created manually or copied from the
first and modifying the data range) force the axis minimum to zero, despite
the absence of anything close to that or any exceptionally high values.

Once again, I could manually establish limits, but I would need to monitor
these for 95 charts with each month's appendage of new data. As an aside, I
do not have a need for the scales on each chart to match one another.

Below I've copied the first two data sets...#1 auto-scales properly, #2 does
not. Values for Set #1: Min=85, Max=120, Major=5, Minor=1, CatCross=85; Set
#2: Min=0, Max=120, Major=20, Minor=4, CatCross=0. Hopefully you can make
sense of the data I've pasted, but one note, there is a blank column between
"Dec" and "Q1TD" and another between "Q4TD" and "YTD". This does not seem to
pose any problem for the first chart.

Thanks in advance for any help!

Mike

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Q1TD Q2TD Q3TD Q4TD YTD

Set #1
Item
1 98 109 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104
Item
2 103 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 101 #N/A #N/A #N/A 101
Item
3 107 115 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 111 #N/A #N/A #N/A 111
Item
4 96 114 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 104 #N/A #N/A #N/A 104

Set #2
Item
1 98 98 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 98 #N/A #N/A #N/A 98
Item
2 101 99 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 100 #N/A #N/A #N/A 100
Item
3 101 110 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 105 #N/A #N/A #N/A 105
Item
4 94 113 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 103 #N/A #N/A #N/A 103

 

[Jon Peltier]

I suppose it matters what you mean by "properly" scaling the axis. By design
(Microsoft's design, that is), if the minimum and maximum differ by more
than about 1/6 of the scale maximum, the minimum defaults to zero.

If you're up for a bit of programming and you have a scaling algorithm you
prefer, you can calculate your own limits in the worksheet and apply them to
the axis:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

- Jon

 

[Guest]

Jon, thanks a lot for your reply and advice. As I trolled the 'net looking
for a solution, I encountered your website as well as the specific page you
have directed me to. I think your approach will work for me just fine. My
hope had been that there was something simple I had overlooked in specifying
settings that didn't require special handling.

I'm still glad I took time to ask the question, because you provided
additional detail with respect to how the y-axis scale is determined. If not
for your reply, I wouldn't know about the approach Microsoft uses and would
continue to be vexed! Thanks again for the extremely valuable help you
provide to the community.