Multiple Comparison Operators?

  • Thread starter Thread starter Zak
  • Start date Start date
Z

Zak

I have a list of about 500 numbers, randomly distributed
between 1 and 40. I want to create "buckets" and
determine how many data points fit in each, such as

Bucket 1: x<5
Bucket 2: 5<x<10
Bucket 3: 10<x<15
Bucket 4: 15<x<20

i'm trying to use the COUNTIF function, which works fine
if i'm only using 1 comparison operator as in Bucket 1
above:

COUNTIF(b3:b521,"<5")

how can i make this work for two comparison operators,
such as in bucket 2? i tried the following, which didn't
work:

COUNTIF(b3:b521,">5,<10")

this returned 0, which is not correct. any ideas?

thanks,
zak.
 
For 5 < X < 10:
=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">=10")
OR
=SUMPRODUCT(--(A1:A100>5),--(A1:A100<10))

For 5 <= X <= 10
=COUNTIF(A1:A100,">=5")-COUNTIF(A1:A100,">10")
OR
=SUMPRODUCT(--(A1:A100>=5),--(A1:A100<=10))

Dan E
 
I have a list of about 500 numbers, randomly distributed
between 1 and 40. I want to create "buckets" and
determine how many data points fit in each, such as

Bucket 1: x<5
Bucket 2: 5<x<10
Bucket 3: 10<x<15
Bucket 4: 15<x<20

i'm trying to use the COUNTIF function, which works fine
if i'm only using 1 comparison operator as in Bucket 1
above:

COUNTIF(b3:b521,"<5")

how can i make this work for two comparison operators,
such as in bucket 2? i tried the following, which didn't
work:

COUNTIF(b3:b521,">5,<10")

this returned 0, which is not correct. any ideas?

thanks,
zak.
Click to expand...

You could also use the FREQUENCY worksheet function. For example, with the
bins in Column D and your data in A1:A500, and the FREQUENCY function
*array-entered* in the range E1:E5:

5 =FREQUENCY(A1:A500,D1:D5) <=5
10 =FREQUENCY(A1:A500,D1:D5) >5 and <=10
15 =FREQUENCY(A1:A500,D1:D5) >10 and <=15
20 =FREQUENCY(A1:A500,D1:D5) >15 and <=20
40 =FREQUENCY(A1:A500,D1:D5) >20

The equivalent COUNTIF formulas would be:

=COUNTIF(A1:A500,"<=5")
=COUNTIF(A1:A500,">5")-COUNTIF(A1:A500,">10")
=COUNTIF(A1:A500,">10")-COUNTIF(A1:A500,">15")
=COUNTIF(A1:A500,">15")-COUNTIF(A1:A500,">20")
=COUNTIF(A1:A500,">20")



--ron
 
Back
Top