1

let suppose that we have following numbers

49  57  38  73  81
74  59  76  65  69
54  56  69  68  78
65  85  49  69  61
48  81  68  37  43
78  82  43  64  67
52  56  81  77  79
85  40  85  59  80
60  71  57  61  69
61  83  90  87  74

and let us suppose we have following class intervals

Lower limit  upper limit
35              41
42              48
49              55
56              62
63              69
70              76
77              83
84              90

what i want is to count how many numbers occurs between given intervals , for instance between [35-41], in excel i have tried following range

=COUNTIF($A$1:$E$10,"<="&J3)-COUNTIF($A$1:$E$10,"<"&I3)

where j3 and I3 are upper and low limit, i think this formula is correct, for instance let us consider following numbers

1    2   3  4  5  6   7  8  9  10  11  12

we would like to know how many elements are in range of 7-12, there are twelve number that are less or equal to 12, and 6 number that are less then 7, so difference between is 6 number, so we have

7 8 9 10 11 12

so i think my formula is correct right?

  • what is the question? If it's just whether your formula is correct, couldn't that more or less easily be checked "manually"? If you want to know how the formula can be improved, then this would be better put to https://codereview.stackexchange.com/ . If on the other hand you have a suspicion that something is wrong with your code, then please give an example where it doesn't work as expected! – codeling Jan 31 '17 at 13:06
  • if I3 is the lower limit, should that not be `COUNTIF($A$1:$E$10,">="&I3)` – Paul Dixon Jan 31 '17 at 13:07
  • @PaulDixon No, it is subtracted from count of numbers `<=J3`, the formula is OK, `COUNTIFS` might be slightly more clean solution. – BrakNicku Jan 31 '17 at 13:10

1 Answers1

2

With data like:

enter image description here

In I2 enter:

=COUNTIF(A$1:E$10,"<=" & H2)

and in I3 enter:

=COUNTIF(A$1:E$10,"<=" & H3)-SUM($I$2:I2)

enter image description here

This takes advantage of the fact that your thresholds do not overlap and have no gaps.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    This can also be achieved using `{=FREQUENCY($A$1:$E$10,$H$2:$H$9)}` as an array formula in formula array `I2:I9`. – Axel Richter Jan 31 '17 at 13:56
  • @AxelRichter **You are correct!** In the general case, your approach is better...I posted my simple approach to show how to take advantage of the non-overlapping thresholds with no gaps. – Gary's Student Jan 31 '17 at 14:03
  • Your example is good. Using `FREQUENCY` is not better in general. I just wanted to mention that there is a `FREQUENCY` function in Excel already. – Axel Richter Jan 31 '17 at 14:07
  • i know frequency function(actually i am teaching excel to my students), what i am trying is to give them simple examples to understand –  Jan 31 '17 at 14:11
  • 1
    @datodatuashvili It may be valuable to give several examples to your students....thanks for the feedback. – Gary's Student Jan 31 '17 at 14:17