Mode function gives single result

  • Thread starter Thread starter Rasmon
  • Start date Start date
R

Rasmon

The mode function displays only the first mode when there is more than one
mode. Is there an easy way to get all the modes of a data set?

Example

1 2 4 4 4 3 6 6 6 5 7 7 0 0 1 7

Should have three modes: 4, 6 & 7.

Thanks.
 
Let A1:A17 house the sample you provided:

{"Num";1;2;4;4;4;3;6;6;6;5;7;7;0;0;1;7}

In B2 enter & copy down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",COUNTIF($A$2:$A$17,A2))

In C2 enter & copy down:

=IF(N(B2),RANK(B2,$B$2:$B$17)+COUNTIF($B$2:B2,B2)-1,"")

D1 houses 1.

D2 houses the formula...

=MAX(IF(INDEX(B2:B17,MATCH(D1,C2:C17,0))=B2:B17,C2:C17))-D1

which must be confirmed with control+shift+enter instead of just enter.

Note that this formulas determines the number of additional mode values.

F2:

=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$17,MATCH(ROW()-ROW($F$2)+1,$C
$2:$C$17,0)),"")

The F-range will show the multiple modes if any.
 
I would use a countif and copy it down like

=COUNTIF($A$1:$A$16,A1)

then filter on the maximum value in B
One could also say that there is no MODE since there is a tie
 
B thru D range worked fine. The F range didn't work out though. Pasted it
in and copied now and only got a value of 1 in F2 and no other values in the
other cells.

Thanks the your help!
 
The array formula should be placed in E1.

Rasmon said:
B thru D range worked fine. The F range didn't work out though. Pasted it
in and copied now and only got a value of 1 in F2 and no other values in the
other cells.

Thanks the your help!



=IF(ROW()-ROW($F$2)+1<=$D$1+$E$1,INDEX($A$2:$A$17,MATCH(ROW()-ROW($F$2)+1,$C
Click to expand...
 
Back
Top