Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

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

  1. #1
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,211
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,211
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    Guys,

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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