I have a spread sheet like this:
ID time Episode mints
1 5:00PM 0
1 6:00PM 1 10
1 6:10PM 0
1 6:30PM 0
2 1:00AM 0
2 2:00AM 1 60
2 3:00AM 0
2 1:00AM 2 30
2 1:10AM 3
2 1:20AM 4
2 1:30AM 0 I want to calculate the time difference between the row has the first non-zero value to the row hits the first 0. For example, in ID1, the duration would be 6:10PM - 6:00PM, which would be 10 minutes.
I somehow got a sense that I should use match() and index() function but after several trials, I got no luck.
1 Answer
A series of nested if's or cocenates might do the trick here, if you say you'll never have more than 2-4 non-0 numbers in a row. Basically, you ask "Do I have a non-0 number? (=if(A1=0,True,False). If it's 0, you don't care, move on. (True =""). If it's not 0, look for the next number, check if that's 0. (=if(B1=0,true, false). If that's 0, great! True=(B2-A2). Otherwise, repeat, but use C1 instead of B1. Nest a few ifs, and you have a clunky, conditional, working solution.
There are better solutions out there, I just can't think of one immediately.
