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

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

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

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

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

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