Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Label XY scatter plot points by frequency of occurence (no pivot t
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 |
#2
|
|||
|
|||
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/ 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to add data points to a scatter plot with trend line | Charts and Charting in Excel | |||
Can I copy x-y scatter plot data direct from one plot to another? | Charts and Charting in Excel | |||
scatter plot & label for a data point | Charts and Charting in Excel | |||
Scatter Graph - Data Label Problems | Charts and Charting in Excel | |||
Fill area beneath a scatter plot | Charts and Charting in Excel |