How can I select a range of numbers in Excel that are between certain other numbers?

Question

I am trying to record the data from a lever press on an Arduino, and transfer it into Excel. Because the Arduino records data in milliseconds when a button is pressed the data looks like this.

(the picture does not show the whole data string but after the lever is released it goes back to 0;1)

So every time the lever is pushed the first number changes from a "0" to a "1". Because I am only trying to find the number of times the lever is pressed, I need to find how many of these "chunks" of "1"s there are (which will give me how many times the button was pressed then released).

I am trying to find a way to select each group of ones in Excel and translate them into another single cell in another column with the value "1", that way I can then just calculate how many "1"s there are in the other column to find the total presses.

Answer 1

You could do:

=SUM( --(FREQUENCY( IF(A1:Axxx="1;1",ROW(A1:Axxx)), IF(A1:Axxx="1;1",0,ROW(A1:Axxx)))>0) )

where A1:Axxx is your range of values. This creates a frequency array based on the number of contiguous "1;1" in your data. e.g. in the pattern shown in the image below, it would create 0..0,3,0..0,4,0..0,1,0..0,2,0..

It then tests if these array values are greater than zero and coerces the results from FALSE/TRUE into 0/1 by using --(formula), so the array now looks like 0..0,1,0..0,1,0..0,1,0..0,1,0... Then the SUM adds up the ones and you have your answer in one cell.

Depending on your version of Excel, you might have to enter the formula with CTRL+Shift+Enter because it is an array formula.

Answer 2

I would make an adjacent column and do something like:

=IF(AND(LEFT(A1,1)="0", LEFT(A2,1)="1"),1,"").

Next I would drag down the formula down, and then sum the resultant range with =SUM(B1:Bxx).