Hard bar (or maybe other) graph

[Basil]

Hiya,

I'm having real difficulty trying to plot a graph, so was hoping you could
help:

I am looking at a coach trip that has 4 stops (it ges in both directions) -
people get on and off at any stop.
Of the trips made, I have derived the percentage utilisation by leg:
(In the following table I have shown the start point down the column - I
have also only shown the one direction (bottom left triangle would show other
direction):

Start Stop
Centre Car Park LGW
LHR
Centre 1% 12%
22%
Car Park 3%
36%
LGW
26%
LHR

I can re-arrange this to make a more suitable data source.

Anyway, the graph I want would have % used on the Y axis, and the 4 points
along the x-axis. It would have bars going across the page from point to
point representing the journey made. Each bar would have varying thickness
dependant on the % use. I'll try and draw it:

%|
|
_____________________
| |
|
| _____________________|____________________|
| |
|
| |
|
| |
|
|_________________|_________________________________________|
|
|
|______________________________________ ___________________|
__|______________________________________|________________________
Centre CarPark LGW
LHR

Now that is a cr*p drawing... but I hope you get what I mean (I would want a
gap between each bar too).

Anyone have any idea?
If you don't think it can be done, please let me know so that I can give up.

Thanks loads,

Basil

 

[Jon Peltier]

Basil -

If you make your "graphics" to fit a narrower page and use a
nonproportional font when laying it out (like courier), we might get a
better image of what you want.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Basil]

Sorry Jon, and thanks for the response... I'll try again:

Table of data:
Journey start down left hand column, journey stop headers on top row. This
only shows the data for the Centre - LHR bus.

Start Stop --
| Centre C.Park LGW LHR
Centre - 1% 12% 22%
C.Park - 3% 36%
LGW - 26%
LHR -

2nd attampt at chart (it's very crude):

%|
| _______
| | |
| |_______|
| ________________
| | |
| | |
| |________________|
|__________________________
| |
|__________________________|
|___________________
__|___________________|_______
Centre C.Park LGW LHR

I've left out the data below 3% (coz it's too hard to try and draw like
this!).
This drawing may not work again, so I'll explain.

I basically want every journey that has data to have a bar on the chart -
spanning from start point to end point (along the x-axis).

I want these bars to be stacked vertically ontop of each other (with a gap
between them - no bar will have the same (horizontal) start and end point.

The thickness (vertically) of each bar will represent what % of the
joourneys had that particular start and end point.

I hope it can be done, if it can't. please let me know.

Thank you for your continued efforts.

Basil

 

[Jon Peltier]

Basil -

I marked your post to remind me to reply, then I got very busy this
week. This can be done, it's not easy, it's a cumbersome method,
requiring major rearranging of the data. The workbook I used to do this
is nowhere to be found.

I can try to recreate this when I get a chance.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Basil]

Thanks for getting back to me Jon.

I shelved the plan to create this graph because I didn't think it was
possible. If it is very complex and time consuming to recreate it, then don't
worry about recreating it for this occasion.

I do think it could be a good one to have on your website (which is
brilliant by the way) if you get the time/desire to revisit it.

Thanks for your responses Jon, and all the help you've offered in the past.
I'll keep my eyes open for anything new on your website!

Basil

 

[Basil]

Thanks for getting back to me Jon.

I shelved the plan to create this graph because I didn't think it was
possible. If it is very complex and time consuming to recreate it, then don't
worry about recreating it for this occasion.

I do think it could be a good one to have on your website (which is
brilliant by the way) if you get the time/desire to revisit it.

Thanks for your responses Jon, and all the help you've offered in the past.
I'll keep my eyes open for anything new on your website!

Basil

 

[Tushar Mehta]

Yep, same here. Well, not losing the workbook, but the cumbersome
nature of the solution. Saved the workbook, decided it was too time-
consuming to explain in a text-only mode, and shelved it pending a
named-formulas approach.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

Basil -

This morning I awoke to unusual inspiration and wrote an article on your
example. It will come out in next month's Tech Trax e-zine, in about a
week. If you want a sneak preview, email me off-line, and I'll send it
to you.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Tushar -

Let me guess, 100 horizontal bars, a la Stephen's FunChrt5.xls? I've
taken a different approach, using stacked areas.

- Jon

 

[AnnieW]

This is an old post so I don't know if this has already been solved, but as I
was posting a question of my own I came across it and I have a graph similar
to this that I created (that wasn't too hard to do).
If you are interested in me sending it in a workbook, please let me know.
Thanks,
Annie Wendt
Epidemiologist
Kalamazoo County, MI
(e-mail address removed)

 

[Del Cotter]

Jon eventually wrote up the solution to this problem in

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=533

What was your technique?