View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default Formula- Count of items that meet a specific range criteria

"arthurbr" wrote:
Perhaps you could use the FREQUENCY function ?

[....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=706|


I concur. However, I disagree with your implementation.

First, I think the "bin" values in column A should be "Excel Dumb's" lower
limits, to wit: 72%, 85%, 90%, 100%, 110%, 119%.

(So there are 6 "bins" as "Excel Dumb" had, not 7 as "arthurbr" has.)

Second, I think the FREQUENCY "bins" range (2nd parameter) should be
$A$2:$A$6, excluding A7, even though the FREQUENCY formula is array-entered
into B2:B7 [1].

That is, there is no need for an "above" bin. The 119% bin will act as
such.

Note that "Excel Dumb" was wrong to label his ranges with an upper bound,
especially the second-to-last range labeled "110% - 118%". In "Excel
Dumb's" example, all of the percentage in A17:D25 are constants. But if
they are computed (probably the case in real-life), 118% is not the
next-lower percentage below 119%.

-----
[1] @"Excel Dumb".... Note that the FREQUENCY formula is array-entered.
You do that by selecting B2:B7, typing the formula
=FREQUENCY(B17:B25,$A$2:$A$6), then pressing ctrl+shift+Enter instead of
just Enter. If you need to edit the formula later, you will probably need
to select B2:B7, edit and delete the formula, then presss ctrl+shift+Enter
to re-array-enter it.