When creating a chart of, e.g. daily sales figures for the month. Does anyone know how to calculate a suitable Y Axis interval?
I've been looking at Excel which seems to choose nice intervals like 100, 200, 500, 1000, 2000 or 5000, etc depending on the numbers involved and the size of the range, but I can't work out what formula or what logic its using to do this.
Anyway it doesn't really matter how Excel is doing it I just need a way to, given some data, work out a suitable interval for the Y Axis.
Best Answer
One solution where the chart can be resized is to figure out the height of the labels and plan to leave about one label-space between them (with rounding of course, say to a multiple of 25 although that depends on the actual label values - if you're graphing US deficit figures, you can probably round to the nearest billion, for example).
Say the chart is 500 pixels high and your graphing values from 0 to 1722. The height of the text labels is 40 pixels. I therefore have 12 "units" on the Y-axis since 12 by 40 is 480, the closest multiple under the height. Add one from that and divide it by two to get the label count (6).
So that would have the exact labels 0, 345, 690, 1035, 1380 and 1725 but, since that would look strange to a human, we round the increments up to 350 to give us 0, 350, 700, 1050, 1400 and 1750. As mentioned earlier, the rounded increment should be based on the actual values rather than fixed amounts.
I would (though this is personal preference) work on having no more than ten labels in a graph so you should round to 10% of the maximum value at least. That's based on many hundreds of BIRT and Cognos reports I've had to put together - you use labels only for the major points, using thin lines across the graph for minor increments.
Now because each of those six labels is 40 pixels high (240 pixels total), there are 260 pixels left to divide among the five gaps between them, at 52 pixels per gap.
You can make the graph look a lot nicer if you ensure the top of the chart is halfway through the maximum label (and the same at the bottom for the minimum label). This means you lose one label height from the graph (20 pixels at top and at bottom) so your label positions are calculated the same but the actual chart goes from 20 to 479 rather than 0 to 499.