+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Pivot tables to generate frequency distributions on qualitative data?

  1. #1
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Pivot tables to generate frequency distributions on qualitative data?

    Dear all,

    I guess my question is fairly easy for someone who knows how to use pivot tables, but it's been keeping me trying unsuccessfully for too long now. Thanks in advance for reading and for your help. I am using Excel 2007 to play with datasets of biological data, and I will try to be as clear as possible in my description. Sorry if this appear a bit simplistic, but that's intended so that I don't confuse everybody.

    For each "individual" (in column), I have different "attributes" (in rows). There are a lot of different versions of "attributes" in the whole population of "individuals", so each "attribute" is given a number (qualitative) so that we can compare which "individual" has which version of the "attribute".

    My question is: is there a way in Excel (and I suspect this may have to do with pivot tables) to generate lists of "attribute" frequency distribution for each "individual"?

    In summary, I have lists like this:

    Please Login or Register  to view this content.
    (Note that there are sometimes values in one individual that are not present in another individual, e.g. "3" in "Individual 2")

    And my goal is to have something like:
    Please Login or Register  to view this content.
    Currently I only manage to do this for single individuals at a time, by putting one column in the "Row labels" field and the same column also in "Values" field of the PivotTable Field List. I don't know how to do this for all my individuals at the same time, the way I do it always seem to give wrong results.

    Thanks a lot for your time and your help.

    All the best,

    G
    Last edited by guillm; 03-26-2012 at 03:51 AM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Pivot tables to generate frequency distributions on qualitative data?

    Welcome to the forum.

    How 'bout just using the FREQUENCY function?
    Please Login or Register  to view this content.
    Select B9:B13, paste =FREQUENCY(B2:B6, $A9:$A13) in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

    Copy across.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Pivot tables to generate frequency distributions on qualitative data?

    It works perfectly. Thanks a lot.

    Minor question: it doesn't seem to work with text (only numbers). Any ideas on how to include text in the frequency distribution? (sometimes I have letters in the dataset, depending on different measurement properties)

    Thanks again.

    G

  4. #4
    Registered User
    Join Date
    02-04-2010
    Location
    GMT-6
    MS-Off Ver
    Excel 2003, 2007
    Posts
    12

    Re: Pivot tables to generate frequency distributions on qualitative data?

    You would need to use a COUNTIF function in lieu of FREQUENCY.

  5. #5
    Registered User
    Join Date
    03-23-2012
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Pivot tables to generate frequency distributions on qualitative data?

    COUNTIF supposes that I know beforehand what values the attributes are? so I suppose there is no way of doing it with FREQUENCY then.
    That was actually my initial intention when I tried pivot tables.

    Thanks both for your help

+ 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