This problem seems easy, but I don’t know if there is a good solution

Occasional Visitor

This problem occurred in the factory.
The number 1 means that the machine is running, and the number 0 means that the machine is stopped. I want to know how many times the machine has stopped, but I can’t just count the times of 0, because between 1 and the next 1, all 0 It only counts once, how can I calculate the problem of how many times the machine has stopped .

The example is as follows : 
1 , 1 , 1 , 0 , 0 , 0 , 1 , 1, 0 , 0,  0 , 0,  0 , 0,  0 , 0,  1 , 1 , 1 , 1 , 1 , 1 , 1 , 0 , 1 , 1 , 1 , 0 , , 0,  0 , 0,  0 , 1 , 1 , 
0 , 0 , 1 , 0 , 1  , 0,  0 , 0, 1, 1 , 0 

 

1 Reply

@Mikey_yu_1980 

You didn't mention how the sequences of zeroes and ones show up in your spreadsheet. Are each of them in single cells in one column, for instance. Or are the all in one cell as a long text string. The attached worksheet contains solutions for both these possible scenarios. Hopefully you can work with one of them.

 

The first uses a helper column where you check "if the current cell equals zero AND the previous cell equals one then the answer will be TRUE, else FALSE. Either of the formulae formula at the bottom returns the number of ones and zeroes that occurred "in pairs", i.e. the number of times TRUE occurred.

 

The other solution takes the length of the original text string, deducts the length of the text where you substituted every occurrence of "1 , 0" (i.e. "space 1 space comma space 0", being 5 characters) with nothing. Divide the result by 5 to find the numbers of times "1 , 0" was substituted. Hence, the number of zeroes following a one.