How many times a value occurs after another value

[butler14]

Hey Folks, I am trying to go through a lot of data values and I need a formula to identify the following:
1. How many times a value occurs after another - e.g in column A row 15 the value 5 occurs, I need to know what is the next value that occurs in row 14 after the value in row 15 and so forth.
2. How many times does this next value in row 14 occurs after the value in row 15 at throughout the data range.

<colgroup><col style="mso-width-source:userset;mso-width-alt:1060;width:22pt" width="29"> </colgroup><tbody>
[TD="class: xl65, width: 29, align: right"]9[/TD]

[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl65, align: right"]9[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]2[/TD]

[TD="class: xl65, align: right"]4[/TD]

[TD="class: xl65, align: right"]3[/TD]

[TD="class: xl65, align: right"]2[/TD]

[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]7[/TD]

[TD="class: xl65, align: right"]1[/TD]

[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl65, align: right"]9[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]2.5[/TD]

[TD="class: xl65, align: right"]9[/TD]

[TD="class: xl65, align: right"]3[/TD]

[TD="class: xl65, align: right"]2[/TD]

[TD="class: xl65, align: right"]1.7[/TD]

[TD="class: xl65, align: right"]1[/TD]

[TD="class: xl65, align: right"]7[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]6[/TD]

[TD="class: xl65, align: right"]5[/TD]

[TD="class: xl65, align: right"]4[/TD]

[TD="class: xl65, align: right"]1.7[/TD]

[TD="class: xl65, align: right"]0[/TD]

[TD="class: xl65, align: right"]3.2[/TD]

[TD="class: xl65, align: right"]5[/TD]

</tbody>

Thanks in advance

 

[Special-K99]

Just to clarify things.

In the 15th row of the table the number 5 occurs. It's preceded by the value 9.
You want to know

1. That the value is 9 (because it's in row 14)
2. That it occurs only one more time after row 15 (it's on row 17).
Is that correct?

So we're not actually looking at row 15 at all are we? We only want the number in row 14, what it is and how many times it occurs beyond row 15.
Yep?


This will do it
1. =A14
2. =COUNTIF(A16:A1000,A14)
This returns the number of occurrences of the value at row 14 after row 15 up until row 1000, increase if necessary.

 

[butler14]

Thanks for the reply.
I want to know that anytime the value 5 occurs what value comes after it at all times and the number of times such value does appear. Currently we use conditional formatting to highlight the number 5 and then count each number that occurs after 5 and how many times such occurrence, that was easy when the data was a few lines, now the data is hundreds of lines and quite obviously time consuming.
1. Yes that the value is 9
2. What is the values that occurs after 5 and the count of such values to determine the frequency of the occurrences.
Using the table, we see that when 5 occurs 9 is the next value but also 5 does have the values 0, 3.2, 6, 5, 7 occurring after it at times. I want to be able to count how many times 9 appears after 5, 0 appears after 5, 3.2 appears after 5., etc. the key value would be 5 and what value occurs after it throughout the data.
Thanks a lot in advance.