Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thanks in advance,
    Erik
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •