Thanks:  0
Likes:  0

# Thread: Counting a within a range

1. I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

Thanks

Nick

2. What is the limit of your numbers that you are counting? Is it all under 100, or an unlimited scope?

3. IF you can add a column somewhere (hidden or otherwise) next to you column of numbers (sazy starting in B1)....with the formula...=TRUNC(A1/10)*10, you can then create a new column, with 0, 10, 20, 30, 40, etc say starting in D1.....then the formula in E1 that would conditionally count your data, based on "tens" is...=COUNTIF(\$B\$1:\$B\$40,D1)......where "40" is the number of rows in your example.

4. On 2002-03-28 07:24, nick350 wrote:
I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

Thanks

Nick
Lets say that A2:A100 houses the data of interest.

In B2 enter: 10
In B3 enter: 20
In B4 enter: 30

In C3 enter: =COUNTIF(\$A\$2:\$A\$100,">"&B2)-COUNTIF(\$A\$2:\$A\$100,">"&B3)

and copy down as far as needed.

5. On 2002-03-28 07:24, nick350 wrote:
I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

Thanks

Nick
Hi Nick,
Another elegant approach would be to use the Analysis ToolPack:
Tools|Analysis ToolPack|Histogram|
In the input range you put your range of numbers.
In Bin range you put the range in which you have the numbers 0,10,20,30,...etc , exampleb1:b10).
If you fill the box properly you'll get the frequency of numbers between 0-10, 10-20, and so on.
Regards, Eli

6. On 2002-03-28 07:24, nick350 wrote:
I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

Thanks

Nick
If by chance you mean...

>=10 x <20
>=20 x <30
>=30 x <40

Then enter a single array formula...

{=INDEX(FREQUENCY(A1:A10,{9;19;29;39}+0.9999999999999),{2;3;4})}

into cells B1:B3.

[ This Message was edited by: Mark W. on 2002-03-28 13:03 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•