Chris -
You could do this with pivots, countif/sumif formulas, array formulas...
There's no magical built-in way to automagically process everyone's
uniquely organized data, because there are too many ways the data is
originally laid out, too many ways it must be arranged at the end, and
too many intermediate paths for the analysis (i.e., you gotta do some
work to make it work).
... and I still can't figure out if proportionally-sized data points
are possible.
It's called a Bubble Chart.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Chris Gregory wrote:
Hi Ed (& Everyone),
I knew I shouldn't have written so much the first time... You've pretty much
understood what I'm trying to do. I'm using your Multiple Chart Builder
(highly recommended, for anyone who hasn't tried it yet), but the problem
would still apply if I was creating one chart. Using a slightly modified
version of what you wrote:
Specie A A A A B B B
Gender 1 2 2 2 1 2 1
Colour 1 2 3 4 1 4 2
A scatter plot for gender (female = 1, male = 2) would show four points: a
male and female point for species A & B. However, looking at the row data,
it's obvious that more males are caught from species A & more females from
species B. Thus, in order for anyone reading the graph (who won't have the
raw tabular data to view) to see what's really going on, I need to either
have proportionally larger points on the graph, and/or be able to label
equivalent values by the number of "repeats".
One problem now is that we have 400 characters, of which ~50 or so would
need countif statements. The number of these characters, and the number of
variations of each (colour, for example) are most likely to grow in the
future. Before writing my last message, I had been thinking about the COUNTIF
solution to the labeling problem, especially for the categorical data. I just
thought that Excel might have an faster, "automatic" way or built in function
to do this, and I still can't figure out if proportionally-sized data points
are possible.
If all this makes more sense & somebody has an idea... Otherwise I'll start
cranking away on all my COUNTIF statements. Thanks for your help,
Chris