Subtract next number in sequence from the one before excel-CodePudding

I want to subtract the next number in the sequence from the previous number so 2 from 1, 4 from 3 and so on. Ideally, it would find those "pairs" and then subtract the time from that row so I also need a way to do that

CodePudding user response：

Assuming there are no sequential "START" options for the same behavior; Something along these lines:

Formula in F2:

=IF(D2="START",XLOOKUP(C2,C3:C$11,B3:B$11)-B2,"")

CodePudding user response：

If I understand well, you want to subtract the timestamps when the status equals "STOP" and the previous status equals "START". For such situation, you might use the following formula:

=IF(AND(D2="START",D3="STOP"),B3-B2,"")

This puts the time difference in that particular case and a blank cell in the other case, resulting in something like this:

Difference
<BLANK>
9.8
<BLANK>
21.3
<BLANK>
12.8
...

(Keep in mind that I've used rounded values, which are slightly different than yours)