• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting number of groups - (Access/Excel) No VBA please

A

Arnav

New Member
I need to count the number of groups of consecutive intervals in Access. I will be more than happy if someone can tell me an elegant way to do it in Excel. Even a single entry of "1" minute counts as a group. A test file is located here http://1drv.ms/1zvQl59 (Also attached "Chandoo Query" with this post) Feel free to download it and experiment with it. The answer I am looking for in the test file is 29 groups. How to do this in Access using QBE only will be a plus, coz I have about 3 million values per sheet and 24 such sheets!!
Thanks,
upload_2014-7-30_18-37-14.png
 

Attachments

  • Chandoo Query.xlsx
    29 KB · Views: 4
Welcome Arnav. "Borrowing" from my friend Narayan in his post here, if you define the time diff range as d, then the array formula is:
=SUMPRODUCT(1*(FREQUENCY(IF(d=1,ROW(d)),(d<>1)*ROW(d))>0))

Remember to confirm using Ctrl+Shift+Enter, not just Enter.
 
Also, are helper columns allowed? If so, a helper column with this formula:
=IF(AND(E3=1,E2<>1),MAX(F$2:F2)+1,"")
and then find the MAX of the helper column. I am not as familiar with QBE/Access, but perhaps this method would be of use there?
 
Thanks!
Sorry, either I am doing something wrong, or it doesn't work :( If you have tried it can you please solve it in the excel sheet and upload it!

upload_2014-7-30_20-44-11.png
 
I'll take a shot at it. Let's say that d is a smaller array, looking like this:
1,1,1,2,3,1,1,4,5,1,1
and as a reminder, this is our formula:
=SUMPRODUCT(1*(FREQUENCY(IF(d=1,ROW(d)),(d<>1)*ROW(d))>0))

The red portion inside the IF will look at our array, and everywhere we have a 1, put the row number, creating this array. This is the first argument of FREQUENCY function
1,2,3,False,False,6,7,False,False,10,11

The blue portion will build an opposite array, putting a row number where the value is not 1. Since it's being multiplied against each other, this will force XL to convert the False's into 0's.
0,0,0,4,5,0,0,8,9,0,0

Now, the tricky/confusing part. FREQUENCY function then takes these two arrays. At each position in the first array, it will calculate how many previous values in 2nd array match. Note that all the numbers in each array are unique, except for 0's/False. So, first we see that there are no 1's, then no 2's, then no 3's, but then there are 3 0's(Falses). The next False comes up, but we've already counted all the previous 0's, so this one gets a zero. Continuing to build the output, we get:
0,0,0,3,0,0,0,2,0,0,0,2

We're getting close! We can see the 3 numbers representing 3 groups of 1's. Now, to get our count, we want to know how many of these numbers are >1. This creates a True/False array
False,False,False,True,False,False,False,True,False,False,False,True

But, we need to SUM these up. Since they are words, in their current form, they would all count as 0. So, our final step is to multiply this array by 1, converting True/False into 1/0
0,0,0,1,0,0,0,1,0,0,0,1

The SUMPRODUCT finally gets to do its job, summing this array, to give us our desired answer of 3.
 
Brilliant! Now what I tried to do was separate the values and bins into two different arrays - and add them in name manager. Doesn't work - I don't even know if it's supposed to!!
 
You must log in or register to reply here.
Back
Top