Thanks:  0
Likes:  0

# Thread: "Between" in excel - coding for number ranges

1. 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 ?!?

2. 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

3. 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.

4. Guys,

Thanks - much more efficient than anything I was trying,

## 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
•