Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
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)),CO UNTIF(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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
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" wrote in message ... 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)),CO UNTIF(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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
In article ,
"Biff" 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the most/least/average occurrence(appear) number?
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). -- cinoV ------------------------------------------------------------------------ cinoV's Profile: http://www.excelforum.com/member.php...o&userid=32016 View this thread: http://www.excelforum.com/showthread...hreadid=517639 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
finding the right number | Excel Worksheet Functions | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
finding the second largest number in a list | Excel Discussion (Misc queries) | |||
finding row number? | Excel Discussion (Misc queries) |