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
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	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	*	*	*

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