Which number appears most, after a specified number

[bines53]

Hello friends !

A column has numbers, I want to know ,which number appears most after No. 10.

Desired number is 2, appears five times After 10.
And the second Desired number is 7 appears 3 times After 10.

Thank you !

 

[Smallman]

Hi bines53

What about number 3, it appears 4 times? Why is 7 ahead of 3?

Take care

Smallman

 

[Somendra Misra]

Hi David,

Here one alternative: I know you will have a resistance for it to use:

=INDEX(ROW($A$2:$A$30)-ROW($A$2),SMALL(IF(FREQUENCY(IF(($A$2:$A$29=10)*($A$3:$A$30)=0,"e",($A$2:$A$29=10)*($A$3:$A$30)),ROW($A$2:$A$30)-ROW($A$2))>1,ROW($A$2:$A$30)-ROW($A$2)+1),ROWS(C$2:C2)))

Confirm with Ctrl+Shift+Enter.

Regards,

 

[bines53]

Hi Somendra,

How do I go from here =1/(1/(index((A2:A29=10)*A3:A30)))) ?

Regards,

 

[bines53]

=1/(1/(index((A2:A29=10)*A3:A30),0)))
I forgot to add 0 to index

 

[bines53]

Hi Somendra,
I started earlier responses outdoors without a computer.

=1/(1/INDEX((A2:A29=10)*A3:A30,0)) I come to
{7;#DIV/0!;2;#DIV/0!;4;#DIV/0!;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;3;#DIV/0!;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;#DIV/0!;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;7;#DIV/0!;2}
What I meant earlier.

This formula is counting the zero, how can I prevent this, the formula will work without zero .
=LARGE(--FREQUENCY(INDEX((A2:A29=10)*A3:A30,0),INDEX((A2:A29=10)*A3:A30,0)),ROWS(H$1:H1))

 

[Somendra Misra]

Hi David,

Will update something on this tonight.

Regards,

 

[Xiq]

Hi bines53,

How about a simple pivot table? See attached.

 

[Somendra Misra]

Hi @Xiq

Glad to see you back.

Regards,

 

[bines53]

Hi Somendra,

Solution with a zero, I can live with that.
IF Someone give a solution without zero, happily.

D3:
=LARGE(--FREQUENCY(($A$2:$A$29=10)*$A$3:$A$30,($A$2:$A$29=10)*$A$3:$A$30),ROWS(H$1:H1))
E3:
=AGGREGATE(14,6,(($A$2:$A$29=10)*$A$3:$A$30)/((FREQUENCY(($A$2:$A$29=10)*$A$3:$A$30,($A$2:$A$29=10)*$A$3:$A$30))=D3),COUNTIF(D$3:D3,D3))


Regards,

 

[John Jairo V]

Hi @bines53 (and all of the participant's here)

Maybe this can be an option. Blessings!

 

[bines53]

Hi John,

You're a genius!

I was just going to go to bed, probably I'll fall asleep with a smile.


Thank you !

David