The next task is to extract

*start time*and

*end time*from column A to calculate elapsed time of each individual event. Start time and End time are located in different rows:

- Start Time: A1, A4, ..., A{N*3+1}
- End Time: A3, A6, ..., A{N*3+3}

where N = 0 to 64.

This article has followed an excellent video describing how to achieve the task using INDEX and ROWS functions in Excel.

### Formula Used

To retrieve start time, here is the formula I have defined in cell J1:

=INDEX($A$1:$A$195, ROWS($J$1:J1)*3)

To retrieve end time, the formula in cell K1 is defined as:

=INDEX($A$1:$A$195,ROWS($K$1:K1)*3-2)

### Details of Formula

__Dollar Sign ($)__
When you copy J1 and paste it to J2, the formula will be changed from

=INDEX($A$1:$A$195, ROWS($J$1:J1)*3)

to

=INDEX($A$1:$A$195, ROWS($J$1:J2)*3)

The reference (i.e., J1) in the formula just point to itself. After being pasted to J2, the reference will automatically be set to J2. But, notice that the following references:

$A$1

$A$195

$J$1

remain the same after being pasted into J2 because we have prefixed them with dollar sign ($). For example, instead of A1, we have named it $A$1.

__INDEX Function__**Array form**- INDEX(array, row_num, [column_num])
**Reference form**- INDEX(reference, row_num, [column_num], [area_num])

Our formula uses the array form by specifying a range of cells as:

$A$1:$A$195

and

*row_num*is defined using ROWS function.

__ROWS Function__ROWS(array)

where array can be an array, an array formula, or a reference to a range of cells for which you want the number of rows.

Our formula defines array as a range of cells. For example, in cell J2, the range is J1:J2 and, in cell J3, the range is J1:J3. In other words, we just count the number of rows from current cell to the first cell in column J. Then we use that count multiplied by three to retrieve every third row from the array.

Our formula defines array as a range of cells. For example, in cell J2, the range is J1:J2 and, in cell J3, the range is J1:J3. In other words, we just count the number of rows from current cell to the first cell in column J. Then we use that count multiplied by three to retrieve every third row from the array.

### References

- TextPad: How to Remove All Lines Except the Ones Containing a Pattern
- Excel Magic Trick 1142: Get Every Other Row with Formula: INDEX and ROWS*2
- EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

## 1 comment:

QUANTUM BINARY SIGNALSProfessional trading signals delivered to your cell phone every day.

Follow our trades NOW and

profit up to 270% per day.Post a Comment