Mode In A list

  • Thread starter Thread starter PRINCE21
  • Start date Start date
P

PRINCE21

I have a list and want to find the most common/least common e
destination ppl visited for holiday how can i do this. So from the lis
the most comon name appears in a different cell box.

I know how do to this for numbers but how can i do it for text.

When using the Max function for numbers how can i put text data next t
it e.g. The computer finds the max number in table then there is
username next to it, hoe can i transfer the username and number to
cell.

Thanx please hel
 
=INDEX(A10:A19,MAX((COUNTIF(A10:A19,A10:A19)=MAX(COUNTIF(A10:A19,A10:A19)))*
ROW(INDIRECT("1:"&COUNTA(A10:A19)))))

Array-entered by Ctrl-Shift-Enter
HTH
 
Here's one way assuming data in one column:

Most common:

=INDEX(A2:A100, MODE(MATCH(A2:A100,A2:A100,0)))

The formula is an array formula, and must be entered
with <Shift><Ctrl><Enter>, also if edited later.
If done correctly, Excel will display the formula in the
formula bar enclosed in curly brackets { } Don't enter
these brackets yourself.

Least common:

=INDEX(A2:A100,MATCH(MIN(IF(FREQUENCY(MATCH(A2:A100,A2:A100,0),
MATCH(A2:A100,A2:A100,0))>MIN(FREQUENCY(MATCH(A2:A100,A2:A100,0),
MATCH(A2:A100,A2:A100,0))),FREQUENCY(MATCH(A2:A100,A2:A100,0),
MATCH(A2:A100,A2:A100,0)))),FREQUENCY(MATCH(A2:A100,A2:A100,0),
MATCH(A2:A100,A2:A100,0)),0))

Also an array formula.


Find username, assuming numbers in D2:D100 and names in C2:C100

=INDEX(C2:C100,MATCH(MAX(D2:D100),D2:D100,0))

just enter with <Enter>
 
Thanx LEO, you helped me a lot,

It must have taken you time to do these formulas as they were big.

All that i can say is thank you again
 
Back
Top