Displaying bivariate frequencies...

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
 
D

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
 
J

Jerry W. Lewis

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
 
Top