Excel: Get Every Third Row with Formula: INDEX and ROWS*3

I have used TextPad to clean up data with bookmark and macro as described in [1]:



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

INDEX function has two forms:

• 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 function has the following syntax:

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.

References

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