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]

Ok, so the problems appear to be related to the translations I attempted into Hungarian.

The function ELTOLÁS is evidently not equivalent to the English OFFSET, and neither is KVÓCIENS equivalent to the English QUOTIENT.

What version of Excel are you using? I used this very good site to translate the formula, and assumed you were on Excel 2010, not thinking it should make much of a difference (formula names rarely change from one version to another).

I've just had a look at another site here, for Excel 2007, according to which QUOTIENT should still be QUOTIENT and OFFSET should be OFSZET.

So perhaps:

=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(OFSZET(A1;0;-SOR(INDIREKT("1:"&OSZLOPOK($A1:A1)))+1));0);1+OSZLOPOK($A1:A1))));QUOTIENT(1+OSZLOPOK($A1:A1)-HAHIBA(HOL.VAN(1;0/($A1:A1=B2));0);2)))

Regards

 

[XOR LX]

N.B. The one drawback to Mourad's site (excel-translator.de) is that it does not translate correctly the separators within array constants; hence my uncertainty in that respect with the formula I posted.

Regards

 

[egotajcs]

Thank you, XOR LX!

I use Office 2007 with an addon program called 'classic menu', but I think, it shouldn't affect the way excel works.
I use a lot of google when I stumble some excel formula questions, and most of the time I can find an answer, so I know that the language versions can cause strange things. But now I started to find the answer with "brute-force" because I didn't find either a 100% accurate excel translator site: so I tried every possible versions.
This is the working solution:

=HA(SZUM(N(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};N(ELTOLÁS(A1;0;-SOR(INDIREKT("1:"&OSZLOPOK($A1:A1)))+1));0);1+OSZLOPOK($A1:A1))));QUOTIENT(1+OSZLOPOK($A1:A1)-HAHIBA(HOL.VAN(1;0/($A1:A1=B2));0);2)))

Look: the N formula remained N formula, don't ask why. :D

XOR, may I ask you how to show the fourth, missing value? For the later part, I think it could be a solution to count backward exact columns +2 and just put it out, but for the first part of the sequence, I cannot count backward because there isn't a previous appearence of the oldest seen value.



upload images

 

[XOR LX]

Good work on finding the correct translation!

Can you just clarify what you mean by "how to show the fourth, missing value?"?

Did you put my formula in A2 and then copy to ALL cells to the right?

Perhaps re-link your spreadsheet so that I can take a look at what you've done.

Regards

 

[egotajcs]

Thank you for your help, XOR!

If I'm copying the formula which is in A2 all the way to the right until BH2, every second cell's result is '1', however, from the first time, it should be '4', because, you know, the number 4 is missing, it is the last number which actually missing. Until Y1. After Y1 when the number 4 is occurs, it should be the number '3' which should be the result.

I would like to measure the distance (which is now workd, THANKS!), and the other variable thing is the actual number which is missing from the four kinds of number.

Here it is my file, it should convert to english or italian for you, because excel has capable to do it for itself:

https://anonfile.com/J5P2Dbb6bd/relarivefreq_missing_value_question_0508.xlsx

 

[XOR LX]

That's because you're still using ELTOLÁS. I believe you should be using OFSZET, as I mentioned earlier.

Regards

 

[egotajcs]

Okay, wow, XOR LX, that was it, indeed!

Thank you very much!
I hope, this thread will come up for a lot of people who looking up for some formulas in excel for examining relative frequency for random sequences.

Just for the record, this was the answer for my question:

array, so CTRL+SHIFT+ENTER:

=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)))

For my hungarian excel 2007 version, this was the real deal:

=HA(SZUM(N(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};N(OFSZET(A1;0;-SOR(INDIREKT("1:"&OSZLOPOK($A1:A1)))+1));0);1+OSZLOPOK($A1:A1))));QUOTIENT(1+OSZLOPOK($A1:A1)-HAHIBA(HOL.VAN(1;0/($A1:A1=B2));0);2)))

And here is the file (with a little conditional forming):

https://drive.google.com/open?id=0B8vn3j6EfIsLanVaYUJyMGpYZTA

Thank you for your help XOR, and your patience about my language-thing.

 

[XOR LX]

Okay, wow, XOR LX, that was it, indeed!

Thank you very much!

You're very welcome!

Thank you for your help XOR, and your patience about my language-thing.

Are you kidding? Your English is excellent!

Maybe ten years ago I would have been able to converse a little with you in your own language, though I fear that if I had attempted any of this conversation in Hungarian we wouldn't have gotten very far...

..ami szégyen, mert a magyar gyönyörű nyelv. Sokszor voltam Magyarországon, sajnos soha nem Szentendrén (bár Pesten többször, valamint Hatvanan és Tatabányán is!).

Egészségedre!

 

[egotajcs]

Hahh! Che piccolo mondo! :D