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.
|