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?

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...


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122756

Hi,

In 2007 you can use

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Demosthenes" wrote:

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?

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


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=122756