MODE of character values

P

Paul Lautman

A colleague just asked how to, from a list of character values, return the
value that appears most often.

@MODE (or =MODE for Excel) does it instantly for numbers, but the formula
that I ended up with for lists of characters was a lot more complicated than
I would have expected it to be.

As an example, in the following list, S should be returned by the formula:

W
S
S
VG
S
S
G
G
W
W

Any ideas?
 
J

Jason Morin

There's definitely a shorter way, but here's what I came
up on the fly:

=INDEX(A1:A10,MAX(IF(MAX(COUNTIF(A1:A10,A1:A10))=COUNTIF
(A1:A10,A1:A10),ROW(A1:A10))))

Array-entered.

HTH
Jason
Atlanta, GA
 
J

Jason Morin

Duh. Man I'm rusty.
-----Original Message-----
One way

=INDEX(A1:A10,MODE(MATCH(A1:A10,A1:A10,0)))


--

Regards,

Peo Sjoblom


more complicated
than


.
Click to expand...
 
P

Paul Lautman

That's really neat Peo. Can you explain:
1) How this works?
2) Why Jason's solution needed an array formula entry but this one doesn't?
3) How you know that the first parameter of MATCH can be a range when the
help says: "Lookup_value can be a value (number, text, or logical value) or
a cell reference to a number, text, or logical value."

I'd really appreciate this as I am in heavy learning mode on Excel.

The solution that I supplied was for Lotus123, which definitely cannot take
ranges as the first parameter of MATCH.

TIA
Paul
 
Top