Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
I have an idea for a chart type that I can almost construct in Excel, except that I am frustrated by the fact that Excel columns have only a single option for borders: all the way around, or none. It would be effectively a graph of interval Y axis against category X axis, where there are many data points per category. This would normally be shown as something like a box and whisker chart, perhaps with outliers shown individually, but I want to show *all* points in the distribution individually. I'm calling this a "comb graph", where the data points are the teeth of the comb: http://www.branta.demon.co.uk/excel/comb071204a.xls This one's constructed using 255 series, formatted identically as columns with no area and a red border. Can anyone suggest a macro to convert the borders around every column in a column chart series into a user-specified *partial* border, e.g. borders on top only, or on top and on the left side only, similar to the way the borders of spreadsheet cells can be specified? I imagine this would work by turning off the borders and replacing them with lines drawn by macro (question: would this method result in lines that did not print in the correct place, as in the Autoshapes problem?) I say I can't construct this in Excel, but what I mean is I can't easily do it using the "Column Chart" type only. The effect is reproducible by constructing a scatter chart using the same data (and much more elegantly, due to not having to use 255 "series" each with only one data point). But I would like to be able to show this to people who have data coming from Microsoft Access in this table form, and who do not have the skills to make a complex chart, but are able to make a column chart. Their final step would be to run the macro which would replace the column borders with the fancy borders. Alternatively, perhaps I *could* arrange for it to be constructed as a scatter graph, but via helper series which are provided wrapped in a point-and-click package that turn a data set looking like the one in the spreadsheet into a series fit for graphing (note that it's not quite as bad as I portray it: some rows actually do have data in more than one column). -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
On Tue, 4 Dec 2007, in microsoft.public.excel.charting,
Del Cotter said: I have an idea for a chart type that I can almost construct in Excel, except that I am frustrated by the fact that Excel columns have only a single option for borders: all the way around, or none. As so often, the solution to a problem I've been mulling for days pops into my head within hours of my posting a question about it :-) I've created a column series on the secondary axis, with no border and *white area*, to mask the vertical borders of the columns. There is one more category of masking column than there is of data columns, and the secondary category axis "Y crosses between categories" is unchecked, so the masking columns are staggered with respect to the data columns. The gap width is set to 90% for both data columns and masking columns, so the data columns effectively have a gap width of about 110%. The bottom border isn't a problem because it's masked by the primary category axis. If I want the appearance of no axis I can set it to White so it can still perform its masking function. Now to automate it all so naive colleagues can maintain it without breaking it. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
Hi Del,
Assuming I have understood your description of the chart I think using xy-scatter and error bars may make for a more controllable chart. I have an example file, based on yours, which I can email if you want. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Del Cotter" wrote in message ... On Tue, 4 Dec 2007, in microsoft.public.excel.charting, Del Cotter said: I have an idea for a chart type that I can almost construct in Excel, except that I am frustrated by the fact that Excel columns have only a single option for borders: all the way around, or none. As so often, the solution to a problem I've been mulling for days pops into my head within hours of my posting a question about it :-) I've created a column series on the secondary axis, with no border and *white area*, to mask the vertical borders of the columns. There is one more category of masking column than there is of data columns, and the secondary category axis "Y crosses between categories" is unchecked, so the masking columns are staggered with respect to the data columns. The gap width is set to 90% for both data columns and masking columns, so the data columns effectively have a gap width of about 110%. The bottom border isn't a problem because it's masked by the primary category axis. If I want the appearance of no axis I can set it to White so it can still perform its masking function. Now to automate it all so naive colleagues can maintain it without breaking it. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
Andy Pope said: Assuming I have understood your description of the chart I think using xy-scatter and error bars may make for a more controllable chart. Andy, thanks for your offer, but I've done it that way before, and it is indeed more controllable... by me. Used by other people, it breaks as they carelessly import new values in or change the formatting. For example, consider the lengths you have to go to to get a category axis with a scatter chart, with dummy axes and the use of the chart labelling add-ins and all. I was searching for a solution less, shall we say, "hotrodded", that other people who are not Excel graph veterans could use and maintain. I think I've found it. I thought of another twist: with *two* clustered columns on the secondary axis, I can choose to mask just the left or right borders on the data columns, giving the appearance of a comb with a "handle" that goes to the base. By carefully making the height of the "non-masking" masking column equal a little less than the minimum of the data values, I get a handle that joins all the data strokes, but floats above the base, just joining the minimum and maximum values. http://www.branta.demon.co.uk/excel/comb071204c.xls Something I would now like is a macro that will take the dreariness out of the stage where I have to go "cursor, F4" a hundred times to copy the identical column format through all those series. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
It may not be as complicated as you think ;)
http://andypope.info/tempfiles/comb071204b.xls What are you going to do with more than 255 data points? Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Del Cotter" wrote in message ... On Wed, 5 Dec 2007, in microsoft.public.excel.charting, Andy Pope said: Assuming I have understood your description of the chart I think using xy-scatter and error bars may make for a more controllable chart. Andy, thanks for your offer, but I've done it that way before, and it is indeed more controllable... by me. Used by other people, it breaks as they carelessly import new values in or change the formatting. For example, consider the lengths you have to go to to get a category axis with a scatter chart, with dummy axes and the use of the chart labelling add-ins and all. I was searching for a solution less, shall we say, "hotrodded", that other people who are not Excel graph veterans could use and maintain. I think I've found it. I thought of another twist: with *two* clustered columns on the secondary axis, I can choose to mask just the left or right borders on the data columns, giving the appearance of a comb with a "handle" that goes to the base. By carefully making the height of the "non-masking" masking column equal a little less than the minimum of the data values, I get a handle that joins all the data strokes, but floats above the base, just joining the minimum and maximum values. http://www.branta.demon.co.uk/excel/comb071204c.xls Something I would now like is a macro that will take the dreariness out of the stage where I have to go "cursor, F4" a hundred times to copy the identical column format through all those series. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Any ideas for making this graph type easy for other users?
On Wed, 5 Dec 2007, in microsoft.public.excel.charting,
Andy Pope said: It may not be as complicated as you think ;) http://andypope.info/tempfiles/comb071204b.xls Thanks, I like the use of MATCH() for x. The use of MAX() for y is less sustainable because the crosstab data sets, out of an Access database that my colleagues are using, don't *always* have just one filled cell in a row, so there needs to be a way to handle that. I think I could adapt it using RANK() instead of MAX(), and multiple x series. It looks like, as in most cases, there is more than one way to skin this cat :-) What are you going to do with more than 255 data points? When that happens, my method would be toast, but to be fair I was deliberately pushing the limits in the example; the actual cases rarely exceed 64 data points. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
### 100% FREE EASY MONEY MAKING IDEAS ### | New Users to Excel | |||
making labels 'Easy to detect' | Excel Discussion (Misc queries) | |||
Making changes to the same worksheet by two users at the same time | Excel Discussion (Misc queries) | |||
Is there an easy way to create automation in a graph? | Charts and Charting in Excel | |||
Making MIcrosoft Word type from Right to Left | Excel Discussion (Misc queries) |