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

Thread: Counting a within a range

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

    Default

    I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

    Thanks

    Nick

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the limit of your numbers that you are counting? Is it all under 100, or an unlimited scope?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IF you can add a column somewhere (hidden or otherwise) next to you column of numbers (sazy starting in B1)....with the formula...=TRUNC(A1/10)*10, you can then create a new column, with 0, 10, 20, 30, 40, etc say starting in D1.....then the formula in E1 that would conditionally count your data, based on "tens" is...=COUNTIF($B$1:$B$40,D1)......where "40" is the number of rows in your example.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-28 07:24, nick350 wrote:
    I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

    Thanks

    Nick
    Lets say that A2:A100 houses the data of interest.

    In B2 enter: 10
    In B3 enter: 20
    In B4 enter: 30

    In C3 enter: =COUNTIF($A$2:$A$100,">"&B2)-COUNTIF($A$2:$A$100,">"&B3)

    and copy down as far as needed.

  5. #5
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 07:24, nick350 wrote:
    I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

    Thanks

    Nick
    Hi Nick,
    Another elegant approach would be to use the Analysis ToolPack:
    Tools|Analysis ToolPack|Histogram|
    In the input range you put your range of numbers.
    In Bin range you put the range in which you have the numbers 0,10,20,30,...etc , exampleb1:b10).
    If you fill the box properly you'll get the frequency of numbers between 0-10, 10-20, and so on.
    Regards, Eli

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 07:24, nick350 wrote:
    I am trying to count how many numbers are within a certain range. I have a series of numbers, and I want to know hom many are more than 10 but less than 20, more than 20, but less than 30, and so on. I have tried. ANy suggestions, would be good.

    Thanks

    Nick
    If by chance you mean...

    >=10 x <20
    >=20 x <30
    >=30 x <40

    Then enter a single array formula...

    {=INDEX(FREQUENCY(A1:A10,{9;19;29;39}+0.9999999999999),{2;3;4})}

    into cells B1:B3.

    [ This Message was edited by: Mark W. on 2002-03-28 13:03 ]

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
  •