How can I record a transition from a 0 to 1 in a column in Excel?

Question

I need to record the number of transitions from a down state (0) to up state (1) in a single column in excel and I am wondering whether there is some kind of logic like this available in Excel or whether I can program this kind of logic in VBA. I am not very well versed in VBA however, so if any links are available, that would be great.

Answer 1

You can get the count with a single SUMPRODUCT formula. If your data is in A1:A25, you could use the following:

=SUMPRODUCT((A1:A25=0)*(A2:A26=1)*(NOT(ISBLANK(A1:A25))))

This counts only instances where one value in the column is 0 (and not blank) and the following value is 1.

Answer 2

My solution is assuming clean data starting in cell A2 and going down column A. For my formula it also assumes no data past row 1000 (adjust this upward for most real world data.

Formula is an array formula so enter using ctrl+shift+enter

=SUM(IF(A2:A1000=0,IF(A3:A1001=1,1,0),0))

How the formula works.

For each cell A2 to A1000 look for a zero, if there is a zero look in the next cell down. If there is a one in the next cell down add one to the count. Note a blank cell will count as a zero.

The end of the ranges (A1000 and A1001) just have to be past the end of the data.

Answer 3

If you only want to keep track of the changes from down to up (assuming your data is in column A) then all you need is =N(A2>A1) then drag the formula down.

This evaluate to TRUE if the value has increased (gone from down to up), then N(boolean) converts to this to 1 or 0.

To keep track of all state changes you'd use =N(A2<>A1)

To count all state changes you'd then sum the column.

Answer 4

You can use formula to detect transitions from 1 to 0 and from 0 to 1. Just be careful around blank cells!

Consider following example (see screenshot below):

column B contains binary data pattern.

C2 will contain following formula:

=IF(AND(NOT(ISBLANK(B3)),B2=1,B3=0),1,0)

D2 will contain:

=IF(AND(NOT(ISBLANK(B3)),B2=0,B3=1),1,0)

You will just need to autofill formula for as many rows as you need in columns C and D.

• Amount of 1 (ones) in columnt C will represent amount of transitions from 1 to 0.
• Amount of 1 (ones) in columnt D will represent amount of transitions from 0 to 1.

And in the end you need is to sum ones (1) in columnds C and D

In example below:

D18=SUM(C2:C16)
D19=SUM(D2:D16)