Measure how far away the last missing value - relative frequency question

[egotajcs]

Hi guys,

I'm examining and mulling over the relative frequency. And I have absolutely no clue, how to solve the following thing.
For example, I have a sequence with values 1, 2, 3, and 4.
I would like to measure how far away the last, missing 4th value after any 3 values occured in the sequence. Of course 3 values can be repeating itself, so the measured distance can be varied.

So, for example, this is is the sequence:
2 1 2 3 1 4 1 3

1. After the first 4 values it is clear that the value '4' is the missing value, and the distance is 4 cells.
2. And then examinig the next value, still the '4' is missing, so at the next cell, the missing value still the '4', and the distance is now 5 cells.
4. And then the '4' occurs, so examining which is the last one missing value, and that is the value '2', the distance is 3 cells.
And so on... The missing value and the distance are change as we moving to the next and the next values in the sequence.

I made an explanatory JPG about this thing and also I attached the excel file.

Can somebody help me about this one?

Thank you in advance!

relarivefreq_missing_value_question.jpg

[Thom7890]

I'm not sure what result you are really after but hopefully my attachment helps

It identifies with yes or no if a series of four numbers has 1,2,3, and/or 4 within it.
For example it quickly identifies that every series contains a 2.

[egotajcs]

Hi Thom7890!

No, it isn't quite like I was seeking after, but I've got help in another excel forum. Because I saw after a short time, nobody's interested in my problem.
But it is really good you posted an answer, because now I reminded that I should post the solution too.

So this is the solution, which has been posted for me on another forum:

https://www.excelforum.com/attachmen...1&d=1494484043