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

[cinoV]

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.

[Biff]

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

"cinoV" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> cinoV
> ------------------------------------------------------------------------
> cinoV's Profile:
> http://www.excelforum.com/member.php...o&userid=32016
> View this thread: http://www.excelforum.com/showthread...hreadid=517639
>

[Biff]

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

"Biff" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "cinoV" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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.
>>
>>
>> --
>> cinoV
>> ------------------------------------------------------------------------
>> cinoV's Profile:
>> http://www.excelforum.com/member.php...o&userid=32016
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=517639
>>
>
>

[R..VENKATARAMAN]

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.




"cinoV" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> cinoV
> ------------------------------------------------------------------------
> cinoV's Profile:
> http://www.excelforum.com/member.php...o&userid=32016
> View this thread: http://www.excelforum.com/showthread...hreadid=517639
>

[Domenic]

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!

In article <[email protected]>,
cinoV <[email protected]> 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.

[Biff]

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

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

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> cinoV <[email protected]> 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.

[Domenic]

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> >Here's an approach that will list the most/least occurring number in a
> >range of cells, including any ties for most/least...
>
> What'da'ya got for the average? <bg>

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>

[cinoV]

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).

If I have a very large data, some formulas will be very helpful.