Any ideas for making this graph type easy for other users?

[Del Cotter]

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]

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.

 

[Andy Pope]

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

 

[Del Cotter]

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.

 

[Andy Pope]

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

 

[Del Cotter]

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.