Counting a within a range

nick350

New Member
Joined
Mar 27, 2002
Messages
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What is the limit of your numbers that you are counting? Is it all under 100, or an unlimited scope?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 , example:(b1:b10).
If you fill the box properly you'll get the frequency of numbers between 0-10, 10-20, and so on.
Regards, Eli
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top