Results 1 to 8 of 8

Thread: Dual Axes

  1. #1
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dual Axes

    Hi,

    I'm using a custom graph type- "Line - column on 2 axes". One y axis has values, the other shows percentages.

    The user wants it changed to 2 columns on 2 axes- but I can't see a way to have 2 labelled axes with any other graph type apart from "Lines on 2 axes", which is even less suitable.

    Is there an alternative?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dual Axes

    Geoff,

    A little trick might achieve what you want. Create an extra column of data with zero values,. In the Chart Wizard, select Line-Column 2 Axis. This should give you the columns you need, plus a line for the zero values, which you can ignore or set the Line Color and Marker Color to none. Attached find a simple sample.

    Andrew
    Attached Files Attached Files

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- solved

    Excellent Andrew! It does just what I want.

    Thanks Heaps
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- still unsolved

    Andrew,

    After sending that solution to the user, he's pointed out to me a problem.

    The values for the second set of bars relate to the first axis only. So the second axis is really just a useless decoration?

    Any thoughts?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- still unsolved

    Geoff,

    What I understood the user wanted a chart that showed the actual values of 2 sets of data, and also the values as a %. Now I am not sure what is required. Has he formatted the second axis as percentage ?

    If I have missed something could you let me know.

    Andrew

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- still unsolved

    Andrew,

    You are right in your assumption. And the fact that the user has seen that it is possible, has fired him up.

    The problem in the graph that you've posted is that the actual size of the columns relates only to the first axis, and not to the second. So for the first two values (cell A3 = 12, cell B3 = 20) the height of the bar reflects 12 and 20. The height of each bar corresponds to the left axis only. The purple bar corresponds to the 12 on the left axis (as I would expect); the bluish/mauve bar (value 20) also corresponds to the 20 on the left axis. I guess I'd like the height of the bar to be equivalent to the 20 (%) on the right hand axis.

    Is that clear? (It's not to me <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>)

    Thanks for your help. Your posting has opened up a lot of possibilities, and I feel myself very close to an answer.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- still unsolved

    Geoff,

    I'm still not sure what is required, but maybe if we know what value actually represents 100%, we might be on our way. Is the second column representative of the same data as the first column, except that it is to be plotted as a percentage ?

    I know how difficult it can be trying to articulate a third party's problem - sometimes it can be difficult articulationg one's own.
    I'm sure some solution, even a kludged one, can be achieved.

    Andrew

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dual Axes- re-solved (and resolved?)

    Andrew,

    The user has come back to me with the solution- using the starting point you gave. I'm attaching the result.

    The height of the second bar in the first pair (value = 20) is the same as the 20% on the right axis, rather than the height of the value 20 on the left hand axis.

    He did this by:
    .Making the source 4 columns
    .Chart type Column/line on 2 axes. The result is a column and a line
    .Selecting the line. Right click on it, select "Chart Type", "Column". This changes the line to a column- which overlaps the first set of columns.
    .Changing the overlap on each set of columns to -100; the gap width on the first to 0, and on the second set to 180.

    I've tried doing the same without the blank columns, but I can't get the separation.

    Thanks for your help. He'd previously told me it was impossible- by showing him your example, he was able go further.
    Attached Files Attached Files
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •