Finding the most/least/average occurrence(appear) number?
Here's an approach that will list the most/least occurring number in a
range of cells, including any ties for most/least...
Assuming that A3 contains your label and A4:A12 contains your numbers...
In B3, enter: Freq
which is just a label
B4, copied down:
=IF(ISNA(MATCH(A4,$A$1:A1,0)),COUNTIF(A4:$A$12,A4) ,"")
In C3, enter: MF-Rank
which is just a label
C4, copied down:
=IF(N(B4),RANK(B4,$B$4:$B$12)+COUNTIF($B$4:B4,B4)-1,"")
In D3, enter: LF-Rank
which is just a label
D4, copied down:
=IF(N(B4),RANK(B4,$B$4:$B$12,1)+COUNTIF($B$4:B4,B4 )-1,"")
In E1, enter: 1
indicating you want the most frequent occurring number
E2:
=MAX(IF(B4:B12=INDEX(B4:B12,MATCH(E1,C4:C12,0)),C4 :C12))-E1
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
In F1, enter: 1
indicating you want the most frequent occurring number
F2:
=MAX(IF(B4:B12=INDEX(B4:B12,MATCH(F1,D4:D12,0)),D4 :D12))-F1
....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
In E3, enter: Most Freq
which is just a label
E4, copied down:
=IF(ROWS(E$4:E4)<=E$1+E$2,INDEX($A$4:$A$12,MATCH(R OWS(E$4:E4),C$4:C$12,0)
),"")
In F3, enter: Least Freq
which is just a label
F4, copied down:
=IF(ROWS(F$4:F4)<=F$1+F$2,INDEX($A$4:$A$12,MATCH(R OWS(F$4:F4),D$4:D$12,0)
),"")
Hope this helps!
In article ,
cinoV wrote:
Hi, I would like to find out what type of formula that I can use to find
the least occurrence number in a column of numbers.
As I know I can use =mode() to find out the most appear number (the
number that come out the most) but I don’t know the formula for the
least appear number and the average appearing number.
Can someone help, thanks.
|