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

**guillm** · 03-23-2012, 01:41 PM

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

**shg** · 03-23-2012, 02:50 PM

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.

**guillm** · 03-25-2012, 10:31 AM

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

**BergShorn** · 03-25-2012, 10:34 AM

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

**guillm** · 03-26-2012, 03:48 AM

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