MODE equivalent for text

  • Thread starter Thread starter Miguel B.
  • Start date Start date
M

Miguel B.

I have a column of cells containing values such as follows:

C3K0
C3E0
C1G0
C7E0
C2M0
C2B0
C2B0
C1H0
C8E0
C1H0

The complete list has approximately 20,000 lines. I'm
trying to generate a formula that will simply identify the
most frequently occurring value(s)(i.e. C2B0). I don't
know what the values will be from month to month so I
can't use the "COUNTIF" function.

Part two of this problem is to identify the 2nd and 3rd
most frequently occuring values in the column as well.

Any help anyone can provide would be much appreciated!

Thanks
 
Hi
try the following array formula for the most common entry
=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))
 
Miguel B. said:
I have a column of cells containing values such as follows:

C3K0 ....
The complete list has approximately 20,000 lines. I'm
trying to generate a formula that will simply identify the
most frequently occurring value(s)(i.e. C2B0). I don't
know what the values will be from month to month so I
can't use the "COUNTIF" function.
Click to expand...

I believe Leo Heuser came up with the array formula

INDEX(TextList,MODE(MATCH(TextList,TextList,0)))

to give the most frequently occurring entry in a single column or single row
list of text strings.
Part two of this problem is to identify the 2nd and 3rd
most frequently occuring values in the column as well.
Click to expand...

If you had put the formula above into, say, X1, then the second most
frequently occurring text entry would be given by the following array
formula in X2.

X2:
=INDEX(TextList,MODE(IF(COUNTIF(X$1:X1,TextList)=0,
MATCH(TextList,TextList,0))))

Fill X2 down to get 3rd, 4th, etc. most frequently occurring text entries.
 
Frank,

Thanks for the formula. I attempted to apply the formula
to the range of cells in question and I received an "#N/A"
result.

My interpretation of the formula you provided was to
insert the range of cells(i.e H8:H16321) everywhere in the
formula where you indicated "Rng." If this was incorrect,
please advise how to revise.

Thanks again!
 
Hi
you have to enter this formula as array formula with CTRL+SHIFT+ENTER

Though you may use Harlan's suggestion. Like his approach (and it
covers the second/third most occurence as well)
 
Back
Top