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

D_Spark

Board Regular
Joined
Feb 4, 2007
Messages
232
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top