Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
2  
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

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer

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

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.