I have a column of numbers which may be either positive or negative numbers. I need to be able to count the number of clusters of positive numbers in that column of numbers where the cluster is = to or greater than 3 consecutive positive numbers. Meaning, if 3,5, 18 occurs consecutively that is a cluster and is counted as 1, as would be 19, 59, 24, 83 counted as cluster 2 and so on. Any help would be appreciated.
Try these array formulas**.
For positives:
=SUM(IF(FREQUENCY(IF(A5:A29>=0,ROW(A5:A29)),IF(A5:A29<0,ROW(A5:A29)))>=3,1))
For negatives:
=SUM(IF(FREQUENCY(IF(A5:A29<0,ROW(A5:A29)),IF(A5:A29>=0,ROW(A5:A29)))>=3,1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Tony,
Thanks for that. Works great.
Chris
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks