y-axis auto range based on data limits
 

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.

y-axis auto range based on data limits
 

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/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

y-axis auto range based on data limits
 

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" wrote:

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/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

y-axis auto range based on data limits
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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" wrote:

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/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

y-axis auto range based on data limits
 

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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" wrote:

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/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.

y-axis auto range based on data limits
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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" wrote:

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/...nkToSheet.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"hlmrspd" wrote in message
...
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.