Rank/Large Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I have a list of randomly repeating values in A1:A100. I would like a
formula in B1:B10 that will return the top 10 used entries in the list in
order of use highest to lowest. It is possible that there may be less than
10 used entries and in that case I would like a 0.
Thanks!
 
Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
 
The value that appears most frequently
Thanks

Gary''s Student said:
Should B1 contain the value that appears most frequently in column A or the
largest value in column A??
Click to expand...
 
Let's start with B2 rather than B1

In B2 enter:
=MODE(A1:A100)

In B3 enter:
=MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100))
this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER.

Copy B3 downwards.
 
I am sorry I may have left out something important. The values are text and
I am getting N/A with text but if I use numbers it appears to work.
Thanks
 
I found A solution. I used ahelpr column with countif and then applied your
formula to that column and then a vlookup with the results of your formula.
Is there a better way to accomplish this?
Thanks!!!!
 
Is there a better way to accomplish this?

Depends on ones definition of better!

rng = A1:A100 = text entries (no empty cells within the range!)

Enter this array formula** in D1:

=IF(COUNTA(rng)<10,0,INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0})))

Enter this array formula** D2 and copy down as needed:

=IF(COUNTA(rng)<10,0,INDEX(rng,MODE(IF(COUNTIF(D$1:D1,rng)=0,MATCH(rng,rng,0)+{0,0}))))

Both formulas will return non-modal entries. That is, unique entries. For
example:

brown
blue
brown
red
red

The formulas will return this array in order:

brown
red
blue

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top