Hi,
I need to calculate durations of certain events and have Excel skip blank cells. I have timestamps in one column, col A (with every row filled in) and in the next column, col B, I have certain events associated with the timestamps. However, the problem is that I don't have every row filled in in the events column. I can't simply sort these data and remove blank rows in the events column because I have other data in the next columns (columns C onwards) that are associated with the timestamps that I need to leave in the Excel sheet. For example, the data look something like this
Time Event
0:17 event A
0:24
1:19
1:31 event B
2:14
3:36 event C
I want to be able to calculate the duration of time for each of the events - how long did event A last, how long did event B last? So I need to subtract the time 1:31 from 0:17 to calculate duration of event A, and so on. But I need to tell Excel that it needs to skip all the blank cells in column B and whenever it finds a cell filled in, it should subtract the time associated with that (in the column to the left of that cell) and subtract that from the time in the original cell. Is there any way to use a collection of formulas to do this in Excel?
Many thanks in advance for any help provided!
SC
I need to calculate durations of certain events and have Excel skip blank cells. I have timestamps in one column, col A (with every row filled in) and in the next column, col B, I have certain events associated with the timestamps. However, the problem is that I don't have every row filled in in the events column. I can't simply sort these data and remove blank rows in the events column because I have other data in the next columns (columns C onwards) that are associated with the timestamps that I need to leave in the Excel sheet. For example, the data look something like this
Time Event
0:17 event A
0:24
1:19
1:31 event B
2:14
3:36 event C
I want to be able to calculate the duration of time for each of the events - how long did event A last, how long did event B last? So I need to subtract the time 1:31 from 0:17 to calculate duration of event A, and so on. But I need to tell Excel that it needs to skip all the blank cells in column B and whenever it finds a cell filled in, it should subtract the time associated with that (in the column to the left of that cell) and subtract that from the time in the original cell. Is there any way to use a collection of formulas to do this in Excel?
Many thanks in advance for any help provided!
SC
