Dynamic y axis on graphs

[freud1]

Hi Guys,

I am pulling a series of data to place into graphs.

I need these graphs to have the same y axis max value as each other, however the data varies so i need this to change when i change the input data.

I cannot use the excel Auto function as this will change the y axis to diferent values for each chart when they need to all show the same value.

I cannot set them as a fixed value because when I change the input data this value may be too high or low.

Any ideas on a solution?

[Andy Pope]

The scales have 2 options auto or fixed.
You need to determine the fixed value for all the scales and then apply that to each chart via code.

Or if the auto option for the chart with the largest values is acceptable you can add a dummy series to each chart that plots that data, thus forcing all the charts to use the same auto values.

[freud1]

thanks for getting back to me andy, i dont quite undserstand what you mean.

I have 5 charts in total that I want to use the same scale, but these charts are populated by the data in the table which changes (from customer to customer)

for example, if it is graphing income, one customer may be spending aprox £10 per week, so i would want the scale at about 12 for all 5 charts, the next customer may spend £1000 per week so i would need the charts all at £1200.

the values on each chart may be weekday spend for one chart, and then weekend spend on another, weekend spend would be much lower but i would still like the chart to show the same scale as the weekday spend

hope this helps.

[Andy Pope]

which bit did you not understand?

Either use a VBA solution to fix all charts to a specific value, based on the data in all charts.

Or add a dummy series to each chart. This series would contain the values of the chart that has the largest value.

Can you create a 2 chart example to illustrate your problem?

[freud1]

thanks Andy,

here is an example sheet.

ideally i need the Max and Min demand charts to use the max demand scale.

and i need the average, max consumption and min consumption to use the scale of the Max Demand devided by 2.....


sorry to make it more complicated

[Andy Pope]

To the min chart I added a series that is a duplicate of the Max series. And set the fill to none.

I did the same for the average charts.

[freud1]

this is exactly what I wanted!

never thought about using hidden series!

Many Many Many thanks!!

[hskalsi]

[QUOTE=freud1;2080801]thanks Andy,
That's fine .
But, How to ensure Y axis Max as the maximum cell value of a range?

[incurabletrekkie]

this is genius! I would never have thought of having an invisible range,thanks guys - another problem solved!

[Yuriy Ososkov]

Hi Andy,

Thanks, but how I can make the y-axis to begin not from zero but from something else: for different clients I have not necessarily y-range to begin with "O" - thanks.

[FDibbins]

Yuriy, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.