mode for text-strings ?

[alcorjr]

Hi everybody,

Is there a function that can return the most frequently appearing text string from a range?

Thanks.

 

[Juan Pablo González]

Not, clear, can you put some examples ?

Wait, I think I understand now... sorry...

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2003-01-14 09:51

 

[Juan Pablo González]

This formula

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

array entered (That is, with Control Shift Enter, not just Enter), will return the (first) most common string in the range Rng

 

[alcorjr]

To Juan Pablo:
Let's say that in a row ( Range a) I have:
12.5x 6x 3xy 5x 3xy 8y 3xy
I'm lookig for something of the sort

= mode(Range a)
In this case it shoud return "3xy"

That is, that it would function with texts in the same way that the MODE function works with values

 

[Juan Pablo González]

Yes, sorry, I understood what you were asking after I re read your post. Look at my answer above...

 

[alcorjr]

To Juan Pablo:
I'm not sure if I understand your formula;
if the array I'm analyzing is A5:A20, I should apply it like this?
=INDEX(A5:A20,MATCH(MAX(COUNTIF(A5:A20,Rng)),COUNTIF(A5:A20,Rng),0))
But then the "Rng" that appears in the parenteses after the coma in the COUNTIF, should'nt be a condtion?

I'm a bit lost. Explain, please

 

[Juan Pablo González]

If yuo range is A5:A20, you have two choices:

1) Change, in my formula all instances of Rng with A5:A20

2) Select A5:A20, go to the name box (Left of the formula bar), type Rng in there and press Enter.

And the COUNTIF uses ALL cells in Rng, that's why is an array formula, because it "loops" through each of them.

 

[alcorjr]

To Juan Pablo: I tried using your formula:
INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))
and it returns #N/A
although in the step-by step window(click on "fx") I see that is giving the correct result. What am I doing wrong?
Thanks and forgive the clumsiness

 

[Juan Pablo González]

Did you press Control Shift Enter when entering the formula in the cell ?

 

[catluvsperu]

This worked REALLY well - though it took me a while to figure out why most of my rows were returning a blank result... til I realized that for many of my rows I had "" values and I wanted to discount those.

Is there a way to apply this code to a range where only populated cells are counted? Or... is there a way other than assigning "" to a cell to force it to be empty?

THANK YOU, I find your suggestions very helpful!