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

#### D_Spark

##### Board Regular
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
NOPQ
1020.5010.4980.502
1030.499
1040.5
1050.503
1060.498
1070.502
1080.499
1090.502
1100.498
1110.497
1120.497
1130.498
1140.504
1150.497
1160.497
1170.498
1180.5
1190.5
1200.498
1210.502

<tbody>
</tbody>
Sheet6

Array Formulas
CellFormula
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.

Last edited:

Replies
2
Views
735
Replies
3
Views
286
Replies
1
Views
271
Replies
1
Views
345
Replies
2
Views
493

1,218,922
Messages
6,145,235
Members
450,603
Latest member
jpbegley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back