Banding data

[Novicesportsperformer]

Hi all,

Wondering if anyone could help.

I have a column of data with values ranging from 0- 30+

I would like to know the number of values between 5-10, 10.1-15, 15.1-20, 20.1-25, 25.1-30, >30

is the are 'countif' statement or something i can produce to find this?

It would be incredibly useful and less time consuming!

Thank you

 

[James006]

Hi,

You should take a look at the Frequency() function ...

Excel FREQUENCY Function

Hope this will help

 

[Novicesportsperformer]

Brilliant thank you!

 

[snjpverma]

Alternately you can also try the COUNTIFS function like this

*	A	B	C	D
Numbers	*	Criteria	Result
*
*	10.1-15
*	15.1 - 20
*	20.1-25
*	25.1-30
*	>30
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"] 5-10 [/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]26[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]22[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]22[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]38[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]29[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]38[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]39[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]23[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =COUNTIFS($A$2:$A$20,">5",$A$2:$A$20,"<=10") D3 =COUNTIFS($A$2:$A$20,">10",$A$2:$A$20,"<=15") D4 =COUNTIFS($A$2:$A$20,">15",$A$2:$A$20,"<=20") D5 =COUNTIFS($A$2:$A$20,">20",$A$2:$A$20,"<=25") D6 =COUNTIFS($A$2:$A$20,">25",$A$2:$A$20,"<=30") D7 =COUNTIFS($A$2:$A$20,">30") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Novicesportsperformer]

couldnt get the frequency to work, this way has worked though for me thank you for your help!

 

[snjpverma]

couldnt get the frequency to work, this way has worked though for me thank you for your help!

Glad to hear things worked for you

 

[Peter_SSs]

You could also possibly use something like this which ..
- Doesn't require individual criteria to be inserted in each formula, so the first formula can just be copied down, and
- Would automatically adjust if you changed the band boundaries without having to adjust the formulas.

Excel 2010 32 bit
	A	B	C	D
1	Numbers		Criteria	Result
2	9		5-10	3
3	26		10.1-15	1
4	22		15.1 - 20	0
5	6		20.1-25	4
6	14		25.1-30	6
7	22		30.1-	5
8	38
9	29
10	34
11	38
12	39
13	25
14	28
15	7
16	28
17	40
18	30
19	28
20	23
Groups

Cell Formulas
Range	Formula
D2	=COUNTIF(A$2:A$20,">="&LEFT(C2,FIND("-",C2)-1))-SUM(D3:D$8)