Finding the most/least/average occurrence(appear) number?

  • Thread starter Thread starter cinoV
  • Start date Start date
C

cinoV

Hi, I would like to find out what type of formula that I can use to fin
the least occurrence number in a column of numbers.

As I know I can use =mode() to find out the most appear number (th
number that come out the most) but I don’t know the formula for th
least appear number and the average appearing number.

Can someone help, thanks
 
Hi!

For the least:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

Numbers in the range A1:A20:

=INDEX(A1:A20,MATCH(MIN(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20),0))

For the average:

Array entered:

=INDEX(A1:A20,MATCH(AVERAGE(COUNTIF(A1:A20,A1:A20)),COUNTIF(A1:A20,A1:A20)))

Note that each formula will return the first instance of any ties!

Biff
 
suppose data is from a2 to a22 with header in A1
keep cursor anywhere in a1 to a22
click data(menu bar)-filter-autofilter
in the arrow in A1 click
click top 10
in <top10 autofilter> window
against <top> click arrow and click <bottom>
in the right small window type
1
click ok and see what you get
do some experiments
top 2 top 3 bottom 2 bottom 2 etc.

may be a function correponding to MODE may be suggested by an expert.
 
Hmmm....

Disregard the average formula.

Are the values integers or decimals or both?

If the average occurrence is 4 but there are no numbers that appear 4 times
but there are numbers that appear 5 times and 3 times, which one should be
the "average"?

Biff
 
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(ROWS(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(ROWS(F$4:F4),D$4:D$12,0)
),"")

Hope this helps!
 
Here's an approach that will list the most/least occurring number in a
range of cells, including any ties for most/least...
Click to expand...

What'da'ya got for the average? <bg>

This was driving me nuts after I "signed off" for the evening! I'm sure the
OP doesn't mean MEDIAN, either!

Biff
 
Here's an approach that will list the most/least occurring number in a
range of cells, including any ties for most/least...
Click to expand...

What'da'ya got for the average? <bg>[/QUOTE]

Zilch! I have no idea what the OP is looking for. <vbg> It would help
if the OP provides an example. I guess we'll have to stay tuned... <bg>
 
Ok here is an example to my question. The following are some data:

2
3
4
2
3
3

No. 2 appear 2 times,
No. 3 appear 3 times,
No. 4 appear 1 time.

From the above data, if I use the formula “ =mode( ) “ it will return
No. 3, as this number is has the most occurrence. The No. 4 is the
least occurrence (it only has appeared one time) and No. 2 is in the
middle.

My question is what formula that I can use to find out the least
occurrence (e.g. No. 4 above) and for middle occurrence (e.g. No.2).
 
Back
Top