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

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.

-
Possible solution: change one graph to the prefered Y AXIS scale and then > copy > paste special> formats. It's not ideal but it's quicker than I had expected. Any other solutions still very welcome. – RocketGoal Aug 20 '10 at 13:08
I'm not 100% sure I understand exactly what you're wanting, but are you just looking for `=CEILING(A1,5)` to round up the values of max? – DMA57361 Aug 20 '10 at 13:17
Yep. Ceiling is what I needed - just found it next to roundup in the help file. Thanks. – RocketGoal Aug 20 '10 at 16:21

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.)

-

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

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

-
Ceiling works. My remaining problem is making a graph scale that does not bump up by the additional 5 extra units (see question). Ceiling gives me 22 but graph gives me an axis limit of 30. That's too much for my space, etc. – RocketGoal Aug 20 '10 at 16:23