Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Stairstep Charts' in Excel (97, SR-1)

    I am constantly being asked for this type of chart, so I'm surprised I've not found some discussion on it. Regardless, I have been asked to create a chart that shows our progress to a goal to the end of the year. Each month, we are planning to improve by 5%. Of course, I can easily draw a line that shows the improvement, but my boss wants to see the improvements discretely, like in my attachment.

    I'd prefer to do this without using macros, since our IT department frowns on their use.

    I've seen these charts produced using ActiveX controls, so I know it's possible with some products. I'd sure appreciate any help anyone might have. Thanks.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    Just use the same Y-value for the first and the last of the month and make a scatter chart. See attached example. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    Just noticed that there is a little glitch in the vertical line: you could fix that by adding 23 hours and 59 minutes to the month-end date. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    Thanks, Sam. I knew it would be simple, and yet everything I tried gave me a slope. The magic is in how you formulated the dates. Now, if I can only figure out how to make it a user defined chart....

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    I started with Sep 1, 01 and Oct 1, 01 in a column and did an autofill to May 1, 02. Then did the same thing with Sep 30 and Oct 31 in another column. Then did drag-n-drops to make a single column, and used the custom format mmm yy to hide the day of the month.

    Looking at the x-scale of my attachment, it wasn't very good. So, I fixed it and added 23 hours to the EOM. Looks better now: I've reattached it.

    > make it a user defined chart
    I don't think that a UDC will be of any use. I can write a macro to automatically create the chart from just the monthly data. Would this help? --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    Yes, you can create a 'custom chart' . Just select your new stair step chart and then go to the Chart menu (click on chart icon - the colored bars) and select 'custom types' tab. At the bottom of this dialog, select the radio button 'user-defined'. Somewhere this is an 'Add' button where you can name the chart.

    Now when you go to the chart wizard you can select this new chart type and it'll take you through the data selections just like a built-in.

    I assume this new chart is stored somewhere, but now where - personal.xls?? Anyone know this answer??

    Deb <img src=/S/chatter.gif border=0 alt=chatter width=38 height=16>

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    Thanks, Sam, but no--I'd rather avoid macros. Besides, I figured out the fault in my thinking: the chart type is actually already defined for us as a line chart; it's the way we formatted the data which is not, yet which defines the chart.
    Interestingly, I also added 23:59:59 to each end date, but over a year's time, there is still a slight angle on every third stair. I can certainly live with that, though. I just can't believe this is not a type of chart Excel can create on its own. Ah, well, thanks to your help, my boss was pleased. What more can I ask? Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: 'Stairstep Charts' in Excel (97, SR-1)

    > I just can't believe this is not a type of chart Excel can create on its own

    You can get very close by using a histogram, with custom data series formatting, no borders and gap width = 0 (See attached spreadsheet)

    StuartR
    Attached Files Attached Files

Posting Permissions

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