Better control of axis ranges

[Guest]

I would like to make x-y charts in Excel 2000 with dynamic source data. That
is, the user can select what data set he wants to display.

To do this, I need better control of how the axis limits are defined. This
can be achieved in one of two ways:

1. Use numbers from cells to define xmin, xmax, ymin, ymax.
2. Define in some way how auto-scaling decides what the min and max values
are (I often find that the data gets "scrunched" into a small portion of the
plot area).

Method 1 is preferred, but 2 may also work. I would prefer to do this
without writing a VBA macro, if possible.

Does anyone know if this can be done?

 

[Andy Pope]

Hi,

Have a look at Tushar Mehta's autochart addin for linking cell values to
the axis max and min values.
http://tushar-mehta.com/excel/software/autochart/index.html

Cheers
Andy

 

[Kelly O'Day]

I have two VBA based methods for controlling x axis limits in my example
file. Take a look, may give you some ideas.

http://processtrends.com/pg_charts_set_x_axis_min__max_dates.htm

...Kelly

 

[Jon Peltier]

For some complex charts, I use a process that completely rescales
everything. It's what graphics packages do behind the scenes, but I use it
in the worksheet to give me ultimate control.

I start by determining min and max values I'll need on an axis. I have
algorithms to choose "nice" axis min, max, and tick spacing values. It's
related to Stephen Bullen's VBA approach:

http://groups.google.com/group/micr...*+author:bullen&rnum=1&hl=en#8a13d5257af6a8c7

but I do it in the worksheet. Then given these calculated limits, I rescale
the series data from 0 to 1 so that 0 corresponds with the calculated min
and 1 to the calculated max; the axis is then locked into a scale of 0 to 1.
That's the data. Then I use this technique from my web site:

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

to add the required axis ticks and labels. No VBA is required, because the
axis scale doesn't change. It sounds like a lot of work, but it's reliable
and flexible, and I can use this to create charts with great complexity,
showing several sections, like the stock charts in the newspaper with prices
in one section, lined up with volume in another, and perhaps a comparison of
various indices in yet another section. If I want to add a section, or
change the relative height of one, it is merely a matter of tweaking a few
parameters in the cells, and all of the scaling occurs automatically.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______