"Between" in excel - coding for number ranges

[PaddyD]

I've got a set of numbers from 0 about 20,000, most less than 500. i want a chart that displays the number of items in the range 0-99, 100-199 etc. Is there a way of doing with without having to code all the alternatives in an elaboate if statment & what's the excel formula for 'between ?!?
As ever, all hlpgratefully received.

 

[Russell]

You could try using Tools->Data Analysis and select Histogram.
You will need a range listing the spread (Bin range) i.e: 100 200 300 etc
Enter the range you want to summarise and the bin range and it will output the count for the bin range set

Russell

 

[Aladin Akyurek]

Lets A1:A7 house the following sample:

{"Nums";
21;
45;
73;
320;
202;
212}

Enter in B from B2 on the bins [0-99,100-199,etc]:

{"Bins";
99;
199;
299;
399}

In C1 enter: Freqs

In C2 enter and copy down as far as needed:

=INDEX(FREQUENCY($A$2:$A$7,$B$2:$B$5),ROW()-1)

You should get in the results area:

{"Freqs";
3;
0;
2;
1}

Note. COUNTIF can also be used to determine freqs, but the above is, I'd say, good enough.

 

[PaddyD]

Guys,

Thanks - much more efficient than anything I was trying,
Paddy