What function will take the average of text data?

M

maluli

I have a range of unit types and I want to count each unit type and return
the one that occurs most often.
 
J

JE McGimpsey

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

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

Note: In cases where two or more types occur with equal, maximum
frequency, the first one in the list will be returned.
 
H

Harlan Grove

JE McGimpsey wrote...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng,rng),0))
Click to expand...
....

Somewhat shorter and more efficient is Leo Heuser's approach,

=INDEX(rng,MODE(MATCH(rng,rng,0)))
 
B

Bob Phillips

Harlan,

JE's formula does have one, significant IMO, advantage over that one, in
that it works if there are empty cells in the range, the second errors out.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
H

Harlan Grove

Bob Phillips said:
JE's formula does have one, significant IMO, advantage over that one,
in that it works if there are empty cells in the range, the second
errors out.
Click to expand...
....

The other formula could be adapted.

=INDEX(rng,MODE(MATCH(rng&"",rng&"",0)))

That'd take care of blank cells. There's still another problem: if there are
only distinct entries in rng, MODE will return an error.
 
B

Bob Phillips

I thought that when I tried it, and I tried it, but it didn't seem to work
then. I wondered if it was down to MODE, but thought I would post and you
might know the solution. Darn thing works now though, must have done it
wrongly.

Bob
 
H

Harlan Grove

Bob Phillips wrote...
I thought that when I tried it, and I tried it, but it didn't seem to work
then. I wondered if it was down to MODE, but thought I would post and you
might know the solution. Darn thing works now though, must have done it
wrongly.
Click to expand...
....

It'd be nice if Excel could treat blank (VB Empty) values consistently.
If rng contains at least one blank cell, MATCH("",rng,0),
MATCH(0,rng,0) and MATCH(IV65536,rng,0) all return #N/A. That leads me
to suspect that Excel always converts blank values in MATCH's 1st
arguement to 0 but doesn't convert the values in MATCH's 2nd argument,
and 0 <> blank in MATCH comparisons.
 
A

Aladin Akyurek

Let A3:A13 house the following sample:

{"Name";"XZA";"XZA";"SDA";"XZA";"SDA";"XFA";"PQA";"PQA";"XFA";"XFA"}

where A3 is a header, while A4, A5, and so on are text values.

The problem: What is the text value which is the most frequent?

A1: N

which is a label.

B1: 1

which signifies "the one that occurs most often".

A2: Ties of the Nth

which is a label.

B2:

=MAX(IF(INDEX(B4:B13,MATCH(B1,C4:C13,0))=B4:B13,C4:C13))-B1

which must be confirmed with control+shift+enter, not just with enter.

B3: Count

which is a label.

B4, copied down:

=IF(ISNUMBER(MATCH(A4,$A$3:A3,0)),"",COUNTIF($A$4:$A$13,A4))

C3: Rank

which is a label.

C4, copied down:

=IF(N(B4),RANK(B4,$B$4:$B$13)+COUNTIF($B$4:B4,B4)-1,"")

D3: Most Freq

which is just a label.

D4, copied down:

=IF(ROW()-ROW($D$4)+1<=$B$1+$B$2,INDEX($A$4:$A$13,MATCH(ROW()-ROW($D$4)+1,$C$4:$C$13,0)),"")

The result that you get in column D is a list, consisting one or more
items...

{"Most Freq";"XZA";"XFA"}

Standard formulas would give you just one item, a result that is not
correct.
 
Top