Is there an EXCEL formula that counts how many times Excel data 'switches' between binomial coding (i.e., data coded as 1 or 2)?

user_13844273 0
Viewed 60

Folks, would really appreciate some help;

If I have the following values of data in Excel (3 examples below) where data is coded as 1 or 2, does anyone know an Excel formula which can count how many 'switches' occur in the sequence of values? What I mean by a 'switch', is when the 1's switch to 2's, and vice versa (when the 2's switch to 1's).

For example;

1, 1, 1, 1, 1, 1, 1, 2, 2 (there is 1 switch here)

1, 2, 2, 1, 1, 1, 1, 1, 1 (there are two switches here)

1, 2, 2, 1, 1, 1, 1, 2, 2 (there are three switches here)

So far, I am able to use the following formula (see below) to see IF there is a switch at all in the sequence (from 2 to 1 for example). But now I am trying to calculate how many switches are occurring in the sequence, and not IF a singular switch is occurring. So I think I possibly need to use a COUNT formula, instead of a FIND formula?

=IF(ISERROR(FIND("21",TEXTJOIN("",TRUE,[data range of a row]))),FALSE,TRUE)

Any help is appreciated.

excel-formula excel
2 Answers

If you have your data in cells A1 to I1, then use this formula:

=SUM(ABS(B1:I1-A1:H1))

I've tested this with your three inputs and it produces the expected answers.

user_2597690

If you would have access to FILTERXML (I believe from Excel 2013 onwards) you could use:

enter image description here

Formula in B1:

=COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>")&"</s></t>","//s[following::*[1]!=.]"))

For your understanding; the xpath expression //s[following::*[1]!=.] will return all (child)nodes where the first following node is different then the current one. Then COUNT will actually count these returned numbers. Note that this will return 0 when no change occurs in your string.

user_97581940
Related