Results 1 to 13 of 13
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graph question (EXCEL 97/2000)

    I don't fully understand what you are after. Could you be a little more specific?

    Are you looking for a "Gantt"-type chart with floating bars?

    The easiest way is to use a stacked bar with the "bottom bar" being hiddent (no fill, no lines). Its value is equal to the min time and max-min is the 2nd bar.

    If you provide some more details, I could provide some more details.

    Steve

    PS
    This website has an example:
    http://www.mrexcel.com/tip052.shtml

  2. #2
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Graph question (EXCEL 97/2000)

    <font color=red>Update: fixed my graph art... <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20></font color=red>

    My data looks like this:

    (three cols)

    Date/time Index1 ID1
    Date/time Index1 ID1
    .
    .
    Date/time Index2 ID2
    .
    .
    Date/time Index3 ID3



    I want a graph that looks like this (X-scale should be date/time so I need XY-plots):

    <pre>| ID3
    | +---+
    | ID1 | |
    | +---+ | |
    | | | ID2 | |
    | | | +---+ | |
    | | | | | | |
    +----------------------------------</pre>


    So basically a bar-chart, the bars themselves may be equal height (their height can also be equal to the Index-number), as long as they are labeled with the ID (once per bar).

    Anyone (I'm looking for a way to do that to a data set in VBA, but a cookbook description on how to do this manually would be more than OK)

    Thanks!

    EJ

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    Yeah... I apologize, created a great-looking ASCII graph (in edit mode) and submitted without checking how it ended up... I can imagine you didn't understand <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    I edited the original post and now it looks much better... hope this helps!

    Erik Jan

    PS1. By the way: I couldn't find a non-proportional font that would allow me to create an ASCII picture easily. Ended-up using the pre-tag.

    PS2. Everytime I post here, I seem to get a new wave of spam... is it me or could email addresses be picked up here and used by others??

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    I do get spam, but I doubt if my address is picked up from here. I strongly suspect the MS Newsgroup as their source. To avoid spam, I use a web access email address over there. If a lot of spam comes in, the max size of my inbox is exceeded and no more are accepted... Once in a while I check it to empty the dust bin <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    Download the xychartlabeller from the Excel MVP page listed below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graph question (EXCEL 97/2000)

    Could you post a file with some representative data. It would help me in understanding the start. I had a completely different idea of what the final looked like!

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    Like it to be something automatic, guess the xychartlabeler requires manual actions each time...

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    Here is an example:

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    What exactly do you mean by automatic. If you make changes to the cells in the range that contains the lables the chart labeller uses, they are reflected on the chart automatically.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    OK, thanks I didn't know that. I'll download it and will try it out (see if it can do what I want).

    The 'automation' part means that I have some VB-code that clear the ranges and pulls new data in (from a different system).

    Erik Jan

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graph question (EXCEL 97/2000)

    Still confused.
    You have 3 products. You have 3 IDs ,though they are NOT linked to a product, (Typo??)
    Product 1 has an ID of 42, Product 2 all have 83, Procduct 3 [sic] has 1 ID of 67, the rest of 83. Should I assume you meant Product 3 and the ID of all should be 67?

    You have 288 DATE/time combos. Do you want a bar chart with 288 bars? What should be the bar height, the product ID? an XY scatter would make much more sense to me than this many bars. But even an XY scatter would need some Y value other than what you are plotting.

    Are you just have all those points to make what looks like VERY wide bars (only 3) and your real question is how to put a label on each "group of bars" only once in the center of the group?

    Add a new column and add the Label for each at the date time position desired (leave all the other rows blank). use the XYchartlabeler to choose this new column for labels.

    If this is NOT what you are doing, please more details: how to turn 289 points into 3 and what is the Y value?

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graph question (EXCEL 97/2000)

    3 ID's are linked to the product, I'm sorry for the typo...

    I want three WIDE bars (the width being equal to the time the product were made - so I'd guess I'd need some sort of XY-chart type)

    Each bar should be labeled ONCE somewhere (on top, in the middle) with the Product ID

    Wrt the bar-height, that's arbitrary... it can be the ID, they can all be equal too

    I'm sorry for all the confusion and appreciate your efforts to help me... hope the above clarification helps a bit...

    Erik Jan

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graph question (EXCEL 97/2000)

    Are you looking for something like this?
    I just created a column chart directly from your data, got rid of the borders and changed the gap width.

    The labels are as I described earlier, I just used col d to be the labels and added the name to a cell near the center. You could do this with an equation, I just eyeballed it.

    Steve

Posting Permissions

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