How to Use a Formula to Return Every Nth Cell in a Column in Excel

It is common for information in an Excel workbook to display data in a repeating pattern so that particular row items appear consistently every Nth row in a column.  For example, you may see something like this:

Date
Customer
Total
Date
Customer
Total

When data is arranged this way, there may be times when you want to copy a particular repeating row's data to another part of the workbook for analysis; sticking with the above example, you may want to copy every 3rd row (the totals) to another worksheet for easy summing.

To accomplish this, you can use the INDEX() function in conjunction with the ROW() function.  The index function "returns either the value or the reference to a value from a table or range."  While you could use other functions, such as OFFSET()  to accomplish the same thing, the advantage to the index function is that is it non-volatile, meaning it doesn't recalculate each time a change is made to the workbook, so your spreadsheet is less likely to become sluggish. The ROW() function, meanwhile, returns the row number of the cell in which the formula is placed.

So say that your data starts in cell A1 of Sheet 1 and that you're copying every 3rd row (the totals) to Sheet2.  You could put the following  formula in cell A1 of Sheet2 and copy it down in as many rows as you deem necessary:

=INDEX(Sheet2!A:A,(ROW()-1)*3)

To see how Excel breaks this down, lets look at the calculation of the first two rows:

=INDEX(Sheet2!A:A,3) ((2-1)*3)= row 3
=INDEX(Sheet2!A:A,6) ((3-1)*3)=row 6

and continues to increment by 3 in each cell where the formula has been copied.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.