+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Chris Gregory
    Guest

    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. #2
    Ed Ferrero
    Guest

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

    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. #3
    Chris Gregory
    Guest

    Re: Label XY scatter plot points by frequency of occurence (no piv


    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. #4
    Jon Peltier
    Guest

    Re: Label XY scatter plot points by frequency of occurence (no piv

    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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1