Comparison formulas
Posted by Matt Poppa on August 03, 2001 9:41 AM
I would like to total information by ranges ie.
80-100, 50-79, 25-49,0-24. This information is all in one column. This is the formula that I was attempting. =Countif(U2:U500,">=80<=100"). What is the correct formula for this type of data retrieval.
Thanks
Matt Poppa
Posted by Aladin Akyurek on August 03, 2001 10:02 AM
Make a list of your criteria in some column (say in V from V2 on), consisting of:
0
25
50
80
100
In W2 enter: =SUMPRODUCT(($U$2:$U$500>=V2)*($U$2:$U$500 < V3))
Copy down this formula as far as needed. It will give you the counts you want (that's anycase what I assumed you're asking for).
Aladin
Posted by Mark W. on August 03, 2001 11:20 AM
Matt, =INDEX(FREQUENCY(U2:U500,{24;49;79;100}),4)
will retrieve the info for 80-100. Unlike other
approaches FREQUENCY can tally all of your
intervals at once. To do his enter the upper
limits of your intervals, {24;49;79;100}, into a
cell range of your choosing (I'll use A1:A4) and
enter the formula, {=FREQUENCY(U2:U500,A1:A4)},
into a 5 cell, vertically oriented range
(I'll use B1:B5). You'll notice that B5 will
contain a count for values >100. Also, note:
that {=FREQUENCY(U2:U500,A1:A4)} is an array
formula which must be entered using the
Control+Shift+Enter key combination.
