Counting a within a range

[nick350]

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

[wi_guy]

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

[wi_guy]

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.

[Aladin Akyurek]

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.

[eliW]

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

[Mark W.]

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 ]