Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
Hi All,
i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
Hi,
i think from these 2 examples you should be able to work out the other formula. =COUNTIF(A1:A20,"<10") =COUNTIF(A1:A20,"=10")-COUNTIF(A1:A120,"20") Mike "mark (plymouth)" wrote: Hi All, i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
Try it this way:
=COUNTIF($A$1:$A$15,"<10") for the first one, assuming your data is in A1:A15. Then this for the next one: =COUNTIF($A$1:$A$15,"="&10) - COUNTIF($A$1:$A$15,""&20) You could then copy this formula down and just change the 10 and 20 to 21 and 30, then 31 and 40 etc to suit your ranges. Hope this helps. Pete On Jul 30, 2:24*pm, mark (plymouth) wrote: Hi All, i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
I did of course mean
=COUNTIF(A1:A20,"=10")-COUNTIF(A1:A20,"20") "Mike H" wrote: Hi, i think from these 2 examples you should be able to work out the other formula. =COUNTIF(A1:A20,"<10") =COUNTIF(A1:A20,"=10")-COUNTIF(A1:A120,"20") Mike "mark (plymouth)" wrote: Hi All, i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
Thanks very much, that works fine.
"Mike H" wrote: Hi, i think from these 2 examples you should be able to work out the other formula. =COUNTIF(A1:A20,"<10") =COUNTIF(A1:A20,"=10")-COUNTIF(A1:A120,"20") Mike "mark (plymouth)" wrote: Hi All, i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count number of instances a number occurs
Assume your data in A1:A15
Create a Bin range B1: 9 B2: 20 B3: 30 B4: 40 B5: 50 B6: 1000 Highlight blank cells C1:C6 then enter formula: =FREQUENCY(A1:A15,B1:B6) ctrl+shift+enter, not just enter "mark (plymouth)" wrote: Hi All, i am trying, without success to creat a formula to do the following: i have a series of values in a column and want to show how manay time a number occurs within a range from the list. i.e. in my list i have 12,17,23,24,25,23,24,22,26,43,32,32,33,44,53 i then want to display the number of instance there is an occurance of: <10, 10-20, 21-30, 31-40, 41-50, 51 I would appreciate any assitance you might offer. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Count the number of times a value occurs within a dt range | Excel Worksheet Functions | |||
Count the number of times a name occurs in a column | Excel Worksheet Functions | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
How do I count the times a number occurs in a given criteria? | Excel Discussion (Misc queries) |