Label XY scatter plot points by frequency of occurence (no pivot t

[Chris Gregory]

Hello,

Thought I was done with my charting questions, but have one more... I have a
spreadsheet with ~100 columns (individual animals, divided equally into 5
species) and ~100 rows (different morphological measurements). When I create
my graphs, I have one graph for each measurement. The species are the x-axis
(1, 2, 3, 4, 5) and the measurement range is the y-axis.

Some of my measurements are categorical, such as color (black = 1, white =
2, yellow = 3, etc.). When I graph my 100 individuals, I see a point for each
color for each species type. This is not surprising as there is a lot of
variation in the color of my species. However, I want to get a better idea of
the number of black individuals, brown individuals, etc. for each species.
Thus, is there a way to have Excel count the number of individuals of each
color & species and then use those values as data labels?

As we are constantly adding characters and individuals, and we have a bunch
of continuous variables within the spreadsheet, I'd rather not deal with
PivotTables or adding the counts to a separate column to label my data. If
labeling in this way is not possible, is it possible to add extra data points
to a graph. For example, one point for every 10 occurences of a variable.
Thus if there were 50 black individuals of species 1, rather than seeing one
data point, the chart would show 5 points next to each other.

Hopefully my question(s) make sense. Thanks for any ideas you might have,

Chris

 

[Ed Ferrero]

Hi Chris,

You have not had an answer yet, so obviously I am not the only person in the
gorup that has no idea what you're talking about. Let's see if I can
guess...

Your data is laid out like this...

Specie Dog Cat Pup Kitten
Colour 1 2 1 1
Measure2

You could add a row above your data and some columns at the end like this...

ColourNo 1 2
Specie Dog Cat Pup Kitten ="Colour" & F1 ="Colour" & G1
Colour 1 2 1 1 =COUNTIF($B$3:$E$3,F1) =COUNTIF($B$3:$E$3,F1)
Measure2

Does that make sense? Or are you trying to do something else?

Ed Ferrero
http://edferrero.m6.net/

 

[Chris Gregory]

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

 

[Jon Peltier]

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/
_______