max number repeated

P

Pivotrend

sup pros

i have many numbers in rows & columns

i want to find out the max number repeated in these set of rows
columns
then the second max number repeated
then the 3rd max number repeated & so o
 
R

RagDyer

Are you talking about MODE()?

=MODE(A1:A100)

Returns the most frequently occurring number.

If you want the second or third or whatever most used number, enter which
occurrence you're looking for into B1, and try this:

=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A100),B1),FREQUENCY(A1:A100,
A1:A100),0))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
R

RagDyeR

If it's not working correctly for you, watch out for "word wrap".

You could try keying it in yourself.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

in message
=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A100),B1),FREQUENCY(A1:A100,
A1:A100),0))
Click to expand...

thanx dude but can you correct this formula
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to locate which cells add up less than or equal to a certainnumber (where numbers can be repeate 1
Max only if the last month is hte highest 4
Need a macro to combine the data from sheets 3
Need a Macro to combine 1-5 sheets in one sheet 1
Max If 1
generating random numbers without repeats 3
Average (exclude 1 min & 1 max Value) 2
Max Length in a row 1

Top