two-dimensional frequency question

[Demosthenes]

i have another peculiar question. Say you have the following data:
A C
B D
B D
B E
A C
B E
A E
B D
B E
A C
B D

and you want to count how many times an "A C" is followed by a "B D," and
how many. So, output for the above would be:
1: 1
2: 1

i think the way to do it would be to sum over a frequency, but i can't
figure out how to set the bin array the way i need it. the bins would have
to start at "A C" and reset after any "A <>C"

thoughts? maybe a way to do it that way or a better way entirely?

 

[NBVC]

If your data is in one column within range A1:A11, then try perhaps:

=SUMPRODUCT(--($A$1:$A$11="A C"),--($A$2:$A$12="B D"))

notice the offset by 1 in the second range..

 

[Shane Devenshire]

Hi,

In 2007 you can use

=COUNTIFS(A1:A11,"A C",A2:A12,"B D")

 

[NBVC]

Although the Sumproduct() version works in both versions and Countifs(
doesn't so you can't go backwards from 2007 with Countifs(

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com