Count number of times changes occur in a row (0 to 1, 1 to 0)

[youra6]

Greetings again,

Lets say I have some hashes with syncable binary flags: 1 being syncable, 0 being unsyncable:

	day1	day2	day3	day4	day5	# of changes
hash1	1	0	0	1	0	3
hash2	0	0	0	0	0	0
hash3	1	1	1	1	0	1

<tbody>
</tbody>

The last column will count the number of times a hash went from (1 to 0) or (0 to 1). In hash1, the change occurred 3 times, in hash3, the change occurred only 1 time.

I've seen formulas that can solve this if the values were transposed into columns. But I've not been able to figure it out when the 1, 0 are in row form.

Any help is appreciated!

 

[StephenCrump]

Assuming your table starts in A1, try:

G2: =SUMPRODUCT(--(B2:E2+C2:F2=1))

 

[youra6]

Assuming your table starts in A1, try:

G2: =SUMPRODUCT(--(B2:E2+C2:F2=1))

That does the trick, thank you! Since its using the SUMPRODUCT Function, I'm guessing there isn't a work around if a cell contains a string value like "NULL"?

 

[StephenCrump]

Probably better to use the more robust:

G2: =SUMPRODUCT(--(B2:E2<>C2:F2))

That does the trick, thank you! Since its using the SUMPRODUCT Function, I'm guessing there isn't a work around if a cell contains a string value like "NULL"?

Does this mean you're looking for results like this:

	A	B	C	D	E	F	G
1		day1	day2	day3	day4	day5	# of changes
2	hash1	1	NULL	0	NULL	1	2
3	hash2	NULL	0	1	0	NULL	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

 

[youra6]

Probably better to use the more robust:

G2: =SUMPRODUCT(--(B2:E2<>C2:F2))



Does this mean you're looking for results like this:

	A	B	C	D	E	F	G
1		day1	day2	day3	day4	day5	# of changes
2	hash1	1	NULL	0	NULL	1	2
3	hash2	NULL	0	1	0	NULL	2

<tbody>
</tbody>

Yes, exactly like you just described! Your suggestion above fixed the NULL issue, I'm going to go QC it to see it still works.

Edit: Ah, because of the change to your formula, it counts NULL as a change as well. Thats not a huge issue but if you can make it work the way your example did, that would be even more fantastic.

 

[Aladin Akyurek]

Yes, exactly like you just described! Your suggestion above fixed the NULL issue, I'm going to go QC it to see it still works.

Edit: Ah, because of the change to your formula, it counts NULL as a change as well. Thats not a huge issue but if you can make it work the way your example did, that would be even more fantastic.

NULL stands probably for an empty (unused) cell, thus as such it does not exist in the data.

 

[StephenCrump]

If you're stuck with "NULL" or similar non-counted values, then a formula approach becomes more difficult, and I'd tend to switch to VBA.

Here's my (quick and dirty!) code:

G2: =CountChanges(B2:F2,"null")
G5: =CountChanges(B5:F5,"")

	A	B	C	D	E	F	G
1		day1	day2	day3	day4	day5	# of changes
2	hash1	null	1	null	1	null	0
3	hash2	null	0	1	null	0	2
4
5	hash1		1		1		0
6	hash2		0	1		0	2

<tbody>
</tbody>

Function CountChanges(rngRow As Range, Optional sExclude As String = "NULL") As Long

    Dim vCurrent As Variant
    Dim lCount As Long, i As Long
    
    lCount = -1
    vCurrent = -1
    For i = 1 To rngRow.Columns.Count
        If rngRow(i).Value <> vCurrent And UCase(rngRow(i).Value) <> UCase(sExclude) Then
            lCount = lCount + 1
            vCurrent = rngRow(i).Value
        End If
    Next i

    CountChanges = Application.Max(lCount, 0)

End Function