Displaying bivariate frequencies...

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

Say I have two columns of data in Excel as follows:

A B
1 1
1 2
1 3
1 1
1 2
1 3
1 1
1 2
1 3
1 1
1 2
1 3
2 1
2 2
2 3
2 1
2 2
2 3
2 1
2 2

I would like to be able to generate the following on an automated
basis, without using macros:

A B Count
1 1 4
1 2 4
1 3 4
2 1 3
2 2 3
2 3 2

Excel (2003) has the capability to give me this info in a Pivot Table
but I was wondering if it's possible to get this in the format shown
above.

Thanks in advance for your suggestion.

Regards,
Drew
 
Care to explain how this works for any two columns of data where I
don't know ahead of time the values, or even the # of
observations/rows?

In other words, if I have a second pair of columns:

C D
Red Blue
Red Blue
Red Green

how would your formula generate:

C D Count
Red Blue 2
Red Green 1

Thanks in advance.

Drew
 
The formula presumes that you know what values are present; it just
determines the number of times that a specified combination occurs, so that
=SUMPRODUCT((C1:C100="Red")*(D1:D100="Blue"))
would return 2. You could use the function UNIQUEVALUES() from Laurent
Longre's MOREFUNC.XLL to determine the unique values in each of the two
columns. It can be downloaded from
http://longre.free.fr/english/

Jerry
 
Back
Top