Thread: Excel chart automatic scale

I created two charts in the second the difference between max and min is greater.
The scale is automatic, but works only in the first

why?

see attach file

In the first chart you set min value of y to 6800.

In the second chart you set min value of y to 0, change that value to 6800 if you whish the two charts to look similar.

Alf

Thanks,
In the two charts, the scale is automatic, I did not set the minimum
Why in the second chart the scale goes to zero?
I would like to be the first chart without changing the minimum

I'm making a chart with the data scrolling, and I need that the axis is changed automatically, without changing the minimum
when the difference between max and min is low, it works (fisrt chart), but when it increases no, why?

Originally Posted by Andy Pope

Add a dummy series to each chart that contains the min and max x & y values from both sets of data. This way the algorithm used to calculate auto scale values will be consistent.

I didn't understand
Give me an example

In this way the two charts have the scale that goes to zero, but I want the opposite

look my example
Sometimes the scale goes to zero, sometimes not, I do not want the scale goes to zero, I do not want to set a min value

I can't download your sample file, but I will say that I accept the default "auto" values about half the time. Not necessarily Microsoft's fault -- they chose an algorithm that they felt would be suitable for most types of data and be acceptable to most people.

I have found that, if you don't like the algorithm Microsoft chose for selecting the auto max and min values, you've either got to enter them manually or write your own algorithm to set the max and min values. Most of the time, I find it easier to just enter the axis limits manually. On the few occasions where I wanted to automate the process, I would:

a) select a few cells in the same worksheet as the plotted data. In those cells, calculate my desired axis limits from the plotted data. I expect this will usually involve the MAX and MIN functions, coupled with a ROUND function, coupled with some "fudge factor" to decide how much I want the axis limits to extend beyond the limits of the plotted data.
b) write VBA code (macro), usually associated with change or calculate event, that would apply those axis limits to the desired axis.

try here
https://rapidshare.com/files/3968531449/807232.xlsx

This is the macro I use

Private Sub CommandButton2_Click()
Dim ChartVar As Chart
Dim lMax As Long, lMin As Long

On Error GoTo ScalingProblem
    'Assigns the values in the Min and Max ranges to variables.
    
    lMax = Sheets("dati").Range("Max").Value
    lMin = Sheets("dati").Range("Min").Value
    
    
    Sheets("grafico").Select
    ActiveSheet.ChartObjects("Grafico 1").Activate
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = lMin
        .MaximumScale = lMax
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
'        .Crosses = xlCustom
'        .CrossesAt = 26000
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
  
Exit Sub
ScalingProblem:
RetrievalProblem:
    MsgBox "Unable to update chart scale.", vbCritical + vbOKOnly, "Scaling Error"

End Sub

The problem is that I'm creating an add-in in VB.net, and insert this macro, creates problems
I can not enter the min max of all data, because I need more zoom, and the data are so many

It's not a question of who is hosting the file, I can't read the newer file format.

I didn't realize this was a part of a larger VB.net project, so I don't know if there is something else I'm not considering.

Just looking at the code, it looks like it should work just fine for one data set, is that right? Or does it fail to do what you want for one data set?

If it works fine for one data set, then it would seem that all you need to do is put the code into some kind of loop (a For/Next loop might be easiest) to go through each data set.

try now...

the problem I have with vb.net is that I can not change the axis with a second button, but we're off topic

I must not be fully understanding your requirements, because it seems so easy to me. What I see happening is you have a large data set (column E) and a small subset of this data set in column G. You are plotting the data in column G to show a subset of the data. The simple answer seems to me to use =MAX(E:E) and =MIN(E:E) to determine the max and min for the entire data set, then use those values to determine and enter (manually or using VB) axis limits based on those extrema. With the axis limits fixed on something suitable for the entire data set, they will then be fixed so the axis limits will stay the same as you look at different subsets of the data.

Originally Posted by Andy Pope

As MrShorty says, the automatic scaling uses the data in the chart to determine scale min/max. Therefore if the data is different between the charts the scales could also be different. You will only be able to automatically get them the same but you will not have a choice in which one they will be the same as due to the data based algorithm.

What you could do is use automatic on both then read the min/max values used and check whether using one set or the other will exclude any data from the other.

thanks

I will see what to do

Originally Posted by MrShorty

I must not be fully understanding your requirements, because it seems so easy to me. What I see happening is you have a large data set (column E) and a small subset of this data set in column G. You are plotting the data in column G to show a subset of the data. The simple answer seems to me to use =MAX(E:E) and =MIN(E:E) to determine the max and min for the entire data set, then use those values to determine and enter (manually or using VB) axis limits based on those extrema. With the axis limits fixed on something suitable for the entire data set, they will then be fixed so the axis limits will stay the same as you look at different subsets of the data.

was just an example, the data are more than 40000 and the window is 50, I need to zoom

thanks anyway