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

<tbody>
[TD="align: center"]102[/TD]
[TD="align: right"]0.501[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"]0.502[/TD]

[TD="align: center"]103[/TD]
[TD="align: right"]0.499[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]104[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]105[/TD]
[TD="align: right"]0.503[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]106[/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]107[/TD]
[TD="align: right"]0.502[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]108[/TD]
[TD="align: right"]0.499[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]109[/TD]
[TD="align: right"]0.502[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]110[/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]111[/TD]
[TD="align: right"]0.497[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]112[/TD]
[TD="align: right"]0.497[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]113[/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]114[/TD]
[TD="align: right"]0.504[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]115[/TD]
[TD="align: right"]0.497[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]116[/TD]
[TD="align: right"]0.497[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]117[/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]118[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]119[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]120[/TD]
[TD="align: right"]0.498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]121[/TD]
[TD="align: right"]0.502[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P102[/TH]
[TD="align: left"]{=IFERROR(MODE(IF(N102:N120=Q102,N103:N121)),INDEX(N:N,MAX(IF(N102:N120=Q102,ROW(N103:N121)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


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.