Most common number that proceeds a specified value in a list

[D_Spark]

I want to enter a formula in cell B1

This formula will show the MODE of the proceeding values that I specify in cell AI

E.g.

If A1=10

Then B1 will show the mode each number in a range B2:B100 that proceed a 10

 

[Rick Rothstein]

...each number in a range B2:B100 that proceed a 10

What do you mean by a number that proceeds a 10? Since we do not know what your data looks like, perhaps if you show us examples of what is in cells B2:B100 and then explain which values are the ones that you consider as proceeding a 10, then we might be able to better help you.

 

[kweaver]

I also suspect the OP means precedes.

 

[Rick Rothstein]

I also suspect the OP means precedes.

I figured that also, but I am still unclear as to what the data layout is, so I thought it would be best for the OP to explain to us what his data looks like and exactly what part of it needs to be evaluated.

 

[D_Spark]

Most common number that follows a specified value in a list

I want to enter a formula in cell B1


This formula will show the MODE of the values in a range that follows a specified number in C1

E.g.

If C1=10

Then B1 will show the mode of numbers that follow 10 in a range B2:B10. Which would be 5.

If C1=5 then B1 would equal 4

eg
B2=10
B3=5
B4=4
B5=10
B6=1
B7=5
B8=4
B9=10
B10=5

 

[D_Spark]

Post re-edited, hope this helps

 

[D_Spark]

Re: Most common number that follows a specified value in a list

I want to enter a formula in cell B1


This formula will show the MODE of the values in a range that follows a specified number in C1

E.g.

If C1=10

Then B1 will show the mode of numbers that follow 10 in a range B2:B10. Which would be 5.

If C1=5 then B1 would equal 4

eg
B2=10
B3=5
B4=4
B5=10
B6=1
B7=5
B8=4
B9=10
B10=5

 

[Rick Rothstein]

Give this array-entered** formula a try...

=MODE(IF(B2:B9=C1,B3:B10))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

NOTE: When adjusting your ranges, note that both ranges contain the same number of cells but are offset from each other by one cell.