Excel: ROUND() to the next unit of 5?

Question

I'm creating a lot of graphs, so I'm making a common scale to be able to compare them using MAX/MIN values that I'm hiding in the graph as a separate series. My Vertical AXIS (Y) is in units of 5.

The problem is that if my MAX is 27.5 then my scale in the graph is going to 35, and not 30. It always bumps up one extra 5. My data ranges (and space) demands I keep things tight, so:

How do I amend my MAX/MIN values/series so it ROUNDS() to the next higher unit of 5?

How do I then limit the graph scale to the next nearest unit of 5?

Hopefully it's not a manual thing with each graph. Any ideas greatly appreciated.

Mike

Answer 1

I think you have to do it using VBA.

1. You can first compute the MAX, MIN and Major Unit value in a cell.
   D1: =CEILING(MAX(B:B),5) gives the next higher unit of 5 (eg. 25)
   D2, D3: Any fix value or formula as you like

2. And then use the following script to link the MaximumScale, MinimumScale and MajorUnit of the chart to the cells.

   Option Explicit
   
   
   Private Sub ChangeAxisScales()
       With ActiveSheet.ChartObjects("Chart 1").Chart
           ' Value (Y) Axis
           With .Axes(xlValue)
               .MaximumScale = ActiveSheet.Range("$D$1").Value
               .MinimumScale = ActiveSheet.Range("$D$2").Value
               .MajorUnit = ActiveSheet.Range("$D$3").Value
           End With
       End With
   End Sub
   

(Alternatively, you can calculate the values in VBA as well.)

Reference link: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Answer 2

@DMA57361 has the right solution, the CEILING function is your friend.

http://office.microsoft.com/en-us/excel-help/ceiling-HP005209007.aspx