y-axis auto range based on data limits

[Guest]

I have a spread sheet with numerous charts. I need the y axis on the charts
to set the minimum appoximately 20% below the lowest data point and set the
maximum appoximately 20% above the highest data point. The major unit should
be set at appoximately 10% of the entire range of data. The spreadsheets are
templates and will have a wide variety data placed into the cells.

 

[Jon Peltier]

Calculate the appropriate values in some cells. Use this technique to apply
the calculated values to the chart's axis:

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

- Jon

 

[Guest]

I've used that technique on the X axis (time) and it works great however, the
spreadsheet is being used as a template and I am putting in wide variations
of data for the Y axis. I have been adjusting the y axis manually on 50
charts everytime I do another data run. For instance one of the charts auto
scales 0 lbm/hr to 120,000 lbm/hr but all of the data is between 100,000 and
107,000. The next scenario I run the data is betwwen 40,000 and 46,000. So I
need the charts to auto scale to 10 - 20 percent outside of the range
everytime I do a run.

 

[Jon Peltier]

What you need to do then is write some formulas that determine reasonable
values for min, max, and major unit. Use these cells in the macro that
adjusts the chart.

- Jon

 

[Guest]

I see how that would work if all of my Y axis were the same. Out of the 50
charts I'll use 10 different Y axis scales. How would I put the charts into
different groups to run the macro?

 

[Jon Peltier]

You could set up a grid with seven columns: Chart Object Name, Ymin, Ymax,
Ymajor, Xmin, Xmax, Xmajor. Each row tracks the data for a given chart. You
need to use Worksheet_Calculate, because Worksheet_Change doesn't respond
when a formula is recalculated, since the formula itself didn't change.
Worksheet_Calculate doesn't distinguish which cell has recalculated either,
so you either have to redo all the axes at each recalc, or add an eighth and
ninth column, where the eighth column is some function of the other six
columns that changes if any value in the rest of the row is recalculated,
and the ninth column holds the previous value of the eighth column, and is
updated whenever the particular axis is updated. This is hard to explain
without making it seem very complicated, but it's really not too bad.

- Jon