Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Format Y-Axis to 'break' (excel 2000)

    Naomi <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    If I understand you correctly, you want to plot two different ranges, one tight like 1,2,3,4,5 and the other big gaps like 125-150-175. I don't think you can do this with one axis. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    I guess the only way to do this is to have two Y-Axies. One for the Low numbers and one for the high numbers. But this is not pretty at all... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Format Y-Axis to 'break' (excel 2000)

    Used to know this one: I need to make the y-axis in my chart "break" to accomodate a number of very low-value bars in addition to one or two very high-value bars. The numbering on the axis, for example, would show even increments - 0,1,2,3,4,5... and the next number shown, reflecting the value of my very high bar, would be 125. If I plot this automatically, the low bars just disappear. I know there's a formula for the y axis display to show only that top number as "125" and the rest automotically. Anyone know what it is? <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    naomiNYNY

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Hi, Wassim, thanks for the response. I know it can be done, but I don't need 2 ranges: I need one break showing one high number above the low range. There's a formula that allows a specific number on the axis, e.g. 6, to READ as "175". That specification, something like [6="175", #,##0] turns the appearance of 6 to 175. The second part of the formula formats negative numbers and the third part of the formula, as I remember, formats all the rest: i.e. the low range. I've just forgotten the specific parameters.

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Format Y-Axis to 'break' (excel 2000)

    Naomi

    I am sorry I misunderstood what you wanted to do. I thought that you wanted to make the numbers look large but actually be large, you only want them to look large.

    You can use any formula for that, if you go into the cell formatting and choose Custom and type Naomi then anything you type in that cell will look like Naomi...

    Oh BTW you need to include it in " " otherwise you will get some weird things like the M in Naomi turns into a Month number... Go figure <img src=/S/confused.gif border=0 alt=confused width=15 height=20>.

    Maybe someone can answer that for us.

    Hope this helps...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Hi! I'm attaching an illustration in excel so you can see what I'm going for. when I tried your "naomi" custom it put "naomi" in every single axis number location and nothing else!

    thanks... <img src=/S/woof.gif border=0 alt=woof width=15 height=15>
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Format Y-Axis to 'break' (excel 2000)

    Naomi

    Sorry if I am being dense today, but now I understand what you want to do.

    I will give you an example and you can then make it work for your workbook.

    In your example you want all the numbers larger than 8 to = 175 so you can use the following custom format: [>8]175;General so now if the cell value is > 8 it will display 175. The number in the cell does not change, if it is 9.1 it will be 9.1 in the cell value but in the cell it will show 175.

    If you want to combine these conditions then you can do, I think two at a time only by repeating the format: [>8]175;[<8]69;General.

    I hope this helps.

    Wassim <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Hi Naomi,
    This may not be exactly what you need but it will work quite nicely for the values you've given! You can change the scale of the Y-axis so that it uses a logarithmic scale (Format Axis-Scale tab, checkbox near the bottom). This will cause your Y-axis to show regular gaps but the markers in this case would be 0, 10, 100, 1000. Try and see if it's OK for you.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Hi Rory -
    appreciate the response but I have no idea how you correlated my values (1 through 6 followed by 175) with a logarithmic scale! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Naomi,
    I didn't! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    It simply happens that if you've got very small values and very large ones, and you want to be able to see all the columns easily on a chart, a logarithmic scale works nicely. Attached workbook will show what I mean - normal and log charts are there for comparison. (I had to set min and crossover to 0.1 for 1 to display!)
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brooklyn, New York, USA
    Posts
    200
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Format Y-Axis to 'break' (excel 2000)

    Hi - it worked for your example, but I used the number 2000 rather than 175, and the axis label appeared as 2009! (the next increment up from 8)

  11. #11
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Format Y-Axis to 'break' (excel 2000)

    Naomi

    Great. I am happy I finally understood what you wanted to do.

    I am sorry it took so many messages, but again. great <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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