How to compute the major unit of a numeric axis

Question

I want to display three labels on the y-axis: min, middle and max. max - min = 9572. The easiest way to find middle is 9572 / 2 = 4786. These values are not nice so Excel would probably increase the interval to 1000 and set middle to 500.

How do I compute nice values for my y-axis when min and max are given? min and/or max might be negative.

Answer 1

Good question! Recently I had solved similar problem recently and what I have done:

1. Create an array of possible steps, something like [1, 2, 5, 10, 20, 25, 50, 75, 100, 200, 250, 500, 750, 1000, 2000, 2500, 5000, 7500, 10000]
2. Determine how many labels you need - I guess it's 3
3. Choose step from an array. 9572 / (3 - 1) = 4786, nearest step is 5000. You may compare distance to next and previous steps and do custom rounding here, for example, if non-rounded step is 20% less than next step, choose next step, otherwise previous one.

4. Choose good looking starting point for min label. Take your actual minimum, divide by chosen step (5000). Round the result (you can use floor for minimum and ceil for maximum) and multiply it by chosen step. For example, if min = -3400 and max = 2700:

   scaleMin = floor(-3400 / 5000) * 5000 = -5000
   scaleMax = ceil(2700 / 5000) * 5000 = 5000

This will give three labels: -5000, 0 and 5000. If you can tolerate last mark on y-axis below graph maximum, don't just use ceil (or floor). Compute how much percents graph maximum is off the mark, and add one step if it's too much.