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

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95
Greetings again,

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

day1day2day3day4day5# of changes
hash1100103
hash2000000
hash3111101

<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!
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,643
Office Version
  1. 365
Platform
  1. Windows
Assuming your table starts in A1, try:

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

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95
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"?
 
Last edited:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,643
Office Version
  1. 365
Platform
  1. Windows
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:


ABCDEFG
1day1day2day3day4day5# of changes
2hash11NULL0NULL12
3hash2NULL010NULL2

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

 

youra6

Board Regular
Joined
Mar 31, 2014
Messages
95

ADVERTISEMENT

Probably better to use the more robust:

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



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


ABCDEFG
1day1day2day3day4day5# of changes
2hash11NULL0NULL12
3hash2NULL010NULL2

<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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,643
Office Version
  1. 365
Platform
  1. Windows
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,"")

ABCDEFG
1day1day2day3day4day5# of changes
2hash1null1null1null0
3hash2null01null02
4
5hash1110
6hash20102

<tbody>
</tbody>
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,160
Messages
5,835,733
Members
430,383
Latest member
Kastore

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top