Counting a series of numbers
Posted by Chris on October 30, 2001 12:31 PM
I have a column of numbers 20.00, 19.08, 20.55, 20.21, and so on. I want to count how many times a number that is >=20.00 and <=20.59 occurs. I can use the countif function to count the number of times one particular number occurs but what do I do to find the count of a series of numbers?
Posted by Barrie Davidson on October 30, 2001 12:42 PM
Try this array formula (instead of pressing enter, press CTRL+SHIFT+ENTER).
{=SUM(((A1:A100)>=20)*((A1:A100)<=20.59)*1)}
Regards,
Barrie
PS - Aladin (I know you'll respond to this one), I think I'm getting the hang of array formulas, COOL!!Barrie Davidson
Posted by Mark W. on October 30, 2001 12:53 PM
Barrie,
{=SUM((A1:A100>=20)*(A1:A100<=20.59))} is
sufficient. The 1st multiplication operator
will coerce the arrays of boolean values into
1s and 0s for the SUM worksheet function.
Posted by Barrie Davidson on October 30, 2001 12:59 PM
My bubble is burst
Back to the drawing board, but you'll have to admit that I'm getting close!
Barrie :)
Posted by Mark W. on October 30, 2001 1:00 PM
Ahh, don't let that burst your bubble... it was a minor technicality
Posted by Aladin Akyurek on October 30, 2001 1:06 PM
> Try this array formula (instead of pressing enter, press CTRL+SHIFT+ENTER).
Yes, they are. Glad to hear you're enjoying it.
You expected me to react. Since your answer is perfect, I'm reacting in a somewhat complementary way.
You can drop *1 from your array formula: multiplication of logical values leads to conversion from logical to numeric
If you replace SUM with SUMPRODUCT, you get also an inherently array formula that does not require control+shift+enter key combinations. That is:
=SUMPRODUCT(((A1:A100)>=20)*((A1:A100)<=20.59))
But, more important, I want to suggest a completely ordinary formula that is applicable in counting situations where a *between* criterion holds:
=COUNTIF(A1:A5,">=20")-COUNTIF(A1:A5,">20.59")
I'd recommend using the COUNTIF formulation for efficiency reasons.
Regards,
Aladin
Posted by Chris on October 30, 2001 1:07 PM
Re: My bubble is burst
It's still not working. I need to count the number of times a number apprears that is between 20 and 21.
Posted by Aladin Akyurek on October 30, 2001 1:12 PM
Chris --
All proposed formulas should work.
Just replace the <=20.59 with < 21 or <=21 in the formula you pick out.
Aladin
===========
Posted by Barrie Davidson on October 30, 2001 1:15 PM
Thanks for input, always fun to learn new approaches (nt)
Posted by Chris on October 31, 2001 6:44 AM
Thank you! New Problem with Counting Numbers
Thank you all for your responses. The formula works great! One problem, if I put the formula into my current document it doesn't work. If I put the formula into a new document it works fine. What could be wrong with my current document?
Posted by Juan Pablo on October 31, 2001 12:00 PM
Re: Thank you! New Problem with Counting Numbers
Most probable cause is the Cell you're putting it into is formatted as text...
Juan Pablo
