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

[egotajcs]

Hi,

This is my very first post, and I have to admit, this is the second place where I look after some help because it seems my goal is not so easy.

I'm examining relative frequency and I have absolutely no clue, how to solve the following thing.
I have a - let's say - sequence with values 1, 2, 3, and 4.
I would like to (1) show the last, missing value, and (2) 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 changing as we procced further in the sequence.


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. Then moving forward to the next next value and examining the last missing value, still the '4' is missing. And the distance changed now to 5 cells.
3. And then the '4' occurs, so examining at this step which is the last one missing value, that is the number '2', and the distance is 3 cells, because the last three sequence is 3,1,4.
And so on... The missing value and the distance are always change as we moving to the next, and the next, and the next etc. values in the sequence.


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


Can somebody help me about this one?


Thank you in advance!

excel file: https://anonfile.com/X66eD4b5b0/relarivefreq_missing_value_question.xlsx

 

[XOR LX]

Hi,

Your results seem inconsistent to me.

For example, using your screenshot, why is G2 equal to 4 but Y2 equal to 3?

From Y2, the latest missing value is 3, which is found in S2, which is 6 cells back from Y2.
A2 is 6 cells back from G2, not 8, so, to be consistent, the result in G2 should also be 3.

Regards

 

[egotajcs]

Hi XOR LX,

Let's see the JPG.
1. Starting from the left (A1): 1, 2, 2, 3. After four number, came three different values. So the distance is 4. The black number. And the missing is 4. That is the red one.
2. Then step further. The next one is 3 again. So the distance is now 5. The vlack 5 number (I2), but still the 4 is the missing value. Red 4.
3. And so on. The number '4' didn't occur until Y1. Just before the finally occurance, the distance was 12 (W2).
4. After finally the missing 4 just occured, then we should remeasure from that particular cell backward, and we can see that nukber '3' is the furthest missing value, because the latest three values were 3,2,1 (S1,U1,W1)
5. And so on.

I am not completely unfamiliar with excel, if someone could tell me some hints, which formulas I should look closely, it would be a step for me to solve it.

Thank you!

 

[egotajcs]

Sorry! I mistyped and don't know ehre to modify:

4. After finally the missing 4 just occured, then we should remeasure from that particular cell backward, and we can see that nukber '3' is the furthest missing value, because the latest three values were 2,1,4 (U1,W1,Y1)

 

[XOR LX]

Thanks, but I'm still not sure I understand the apparent inconsistency. You are saying that the 'distance' from A1 to G1 is 4, but that the 'distance' from S1 to Y1 is only 3. Yet these two ranges contain precisely the same number of cells.

Regards.

 

[egotajcs]

I think it is my english which causes misunderstandings.
Always look backward: from right to left.
First is A1. Then move forward. C1. When C1's value occurs, now we have two occurances, two different values. Great, move one step to right, E1. It is again 2. It means the distance is now 3 (again, looking backward), but still 2 kinds of values occured from four. Let's move forward: G1. The third kind of value appeared. And the distance is four. From G1 to A1. (again: always looking backward).

When Y1's value appears, hence we check the distance and the furthrst midsing value, we have 2,1,4 (U1,W1,Y1), and the distance is three, from Y1 to U1.

Imagine the numbers as it is on a timeline. Theoretically, we should seen every values 25%. But the frequency is the key. And I would like to measure the frequency.

 

[XOR LX]

In A2, array formula**:

=IF(SUM(N(COUNTIF($A1:A1,{1,2,3,4})>0))<3,"",IF(A1="",MATCH(1,0/FREQUENCY(0,1/IFERROR(MATCH({1,2,3,4},N(OFFSET(A1,0,-ROW(INDIRECT("1:"&COLUMNS($A1:A1)))+1)),0),1+COLUMNS($A1:A1)))),QUOTIENT(1+COLUMNS($A1:A1)-IFERROR(MATCH(1,0/($A1:A1=B2)),0),2)))

Copy across as required.

Note that, if you are not using an English-language version of Excel then, as well as making the necessary amendments to the function argument separators within the above (e.g. replace commas with semicolons), you may also need to amend the separator within the array constant being passed to MATCH and COUNTIF, i.e.:

{1,2,3,4}

If, for example, you are using a Hungarian-language version of Excel, then I'm afraid I'm not sure what the separator within horizontal vectors is. In Italian (post-2007) versions of Excel, for example, the above would be represented thus:

{1\2\3\4}

I cannot say for sure whether this also applies to Hungarian versions of Excel, though I've a suspicion it might be a period, i.e.:

{1.2.3.4}

As a guess:

=HA(SZUM(S(DARABTELI($A1:A1;{1.2.3.4})>0))<3;"";HA(A1="";HOL.VAN(1;0/GYAKORISÁG(0;1/HAHIBA(HOL.VAN({1.2.3.4};S(ELTOLÁS(A1;0;-SOR(INDIREKT("1:"&OSZLOPOK($A1:A1)))+1));0);1+OSZLOPOK($A1:A1))));KVÓCIENS(1+OSZLOPOK($A1:A1)-HAHIBA(HOL.VAN(1;0/($A1:A1=B2));0);2)))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[Tetra201]

Try putting your numerical sequence in a contiguous range (i.e., without empty cells between the numbers), say A1:AD1,
then use the following UDF to get the distance, =HowFar($A$1:A1,"d"), and the missing value, =HowFar($A$1:A1,"v").

Function HowFar(rng As Range, out As String) As Variant
    Application.Volatile
    Dim Dist(1 To 4) As Long
    Dim s As String
    Dim i As Byte
    If rng.Columns.Count < 4 Then HowFar = "": Exit Function
    s = Join(Application.Transpose(Application.Transpose(rng)), "")
    For i = 1 To 4 Step 1
        Dist(i) = rng.Columns.Count - InStrRev(s, i)
    Next i
    Select Case out
        Case "d": HowFar = Application.Max(Dist)
        Case "v": HowFar = Application.Match(Application.Max(Dist), Dist, 0)
        Case Else: HowFar = CVErr(xlErrNA)
    End Select
End Function

 

[egotajcs]

Hi XOR LX!

Wow, thank you very much! You are absolutely amazing to find an english-hungarian formula dictiionary! :D Man, you're great!
But something's missing, because at G2, it came with an error result right after it should come with a result. I guess an IFERROR is missing? I'm not sure how this nice array works, so I'm not sure, how to fix.

May I ask you to open the excel file and take look upon it?
I uploaded the correspondant excel file here:

https://anonfile.com/v8KbD3b2bf/relarivefreq_missing_value_question_almost.xlsx

 

[egotajcs]

Hi Tetra201,

This is a problem: unfortunately I cannot put the numerical sequence in contiguous range, as a matter of fact, there are more gaps between each numbers because I investigating the frequency with some of other aspects using some other formulas, not just this approach.
However, I like it very much, and despite I never used VBA, but I can google, I think I could find by google where to copypaste a VBA code and how to change if I should examine not just from 1 to 4, but for 1 t 10, and so on.
But the gaps between the numbers are inevitable, I'm afraid.

Is there a way to make it blank cell safe?

Much appreciated!