# Thread: Counting ranges of values (2003/SP2)

1. ## Counting ranges of values (2003/SP2)

Attached is a small file with data like the data I'm working with. My goal is a table like this:

Number of occurrences
id_num | 0-3 | 4-6 | 7-9
121 | 8 | 3 | 3
125 | etc.
130

...In this table the data would be counts: how many entries with id_num 121 have value between 0 and 3, how many between 4 and 6, etc.

I've been trying pivot tables and functions but can't figure either one out. Could an expert help?

Erik

2. ## Re: Counting ranges of values (2003/SP2)

The first group consists of 4 numbers (0, 1, 2 and 3), the others of 3 numbers (4, 5 and 6, then 7, 8 and 9 etc.). Is this inconsistency intentional?

3. ## Re: Counting ranges of values (2003/SP2)

I just made the ranges up for this example. In my real data, the categories are <12, 12-1000, and >1000.

Thanks,
Erik

4. ## Re: Counting ranges of values (2003/SP2)

See the attached version. I added a calculated column to define a group number; it uses the boundaries in F1 and F2.
The formula in C2 is =3-(B2<=\$F\$1)-(B2<=\$F\$2), this has been filled down.
I created a pivot table from these extended data.
If you alter the data or edit the values in F1 and F2, you must refresh the pivot table to see the results.

5. ## Re: Counting ranges of values (2003/SP2)

A great help. Many thanks. This is the first time I've ever dealt with pivot tables (yes, I'm really an Excel novice) so your example was extremely useful.

Thanks,
Erik

6. ## Re: Counting ranges of values (2003/SP2)

If you change the formula in Hans' workbook for Cell C2 to:

=CHOOSE(3-(B2<=\$F\$1)-(B2<=\$F\$2),"Value <="&\$F\$1, \$F\$1&"< Value <="&\$F\$2,"Value >"&\$F\$2)

And copy/Autofill it to C3:C35 and then REFRESH DATA in the pivot table you will get "headings" indicating the divisions inistead of the "group numbers" Hans used (1-3). This will help make the results more "intuitive".

Steve

#### Posting Permissions

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