VERY CHALLENGING: Help with formula to determine the most common number that follows a pre-determined value

[D_Spark]

I would some assistance with creating a formula to determine the most common number that follows a pre-determined value.

I have a series of values in cells N102:N121.....(see below)
In cell P102, I want a formula to determine the most common number that proceeds a value typed into Q102

Eg if I type a value of 0.47 into Q102 using the series below then the most common value to follow 0.47 is
0.497.

if I type a value of 0.502 into Q102 using the series below then there is no most common value to follow. This as there is one occurrence of 0.499 and one of 0.498.....in this case the last value the followed 0.502 would be shown, i.e. 0.498



0.501
0.499
0.5
0.503
0.498
0.502
0.499
0.502
0.498
0.497
0.497
0.498
0.504
0.497
0.497
0.498
0.5
0.5
0.498
0.502

 

[Eric W]

I think you had a typo with your first example, since there is no .47 in your data set. But it works if you meant .497. Here's one possibility:

Excel 2010

	N	O	P	Q
102	0.501		0.498	0.502
103	0.499
104	0.5
105	0.503
106	0.498
107	0.502
108	0.499
109	0.502
110	0.498
111	0.497
112	0.497
113	0.498
114	0.504
115	0.497
116	0.497
117	0.498
118	0.5
119	0.5
120	0.498
121	0.502

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula P102 {=IFERROR(MODE(IF(N102:N120=Q102,N103:N121)),INDEX(N:N,MAX(IF(N102:N120=Q102,ROW(N103:N121)))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

One quirk: if you enter a number in Q102 that is not in the N102:N121 list, then it will return the value in N1.

Let me know how this works.