Operations On Every Nth Row

This page describes formula you can use to work with every Nth row on a worksheet.

Introduction

You may find in your projects that you need to get data from every Nth row in a column of data. For example, you may want to retrieve a value from every third row from a column of blocked format data, or you may want to aggregate that data with SUM or AVERAGE. This page describes how to do this. All of the formulas on this page are Array Formulas, so you must enter then by pressing CTRL SHIFT ENTER rather than just ENTER.

Returning Every Nth Row

You can use the Array Formula below to return every Nth row from a column of values. Enter the formula (pressing CTRL SHIFT ENTER rather than just ENTER) in a cell and fill down for as many rows as there will be data. In the formula, the name StartList refers to the first cell in which the formula is entered. The name Data refers to the column of data whose values will be returned, and EveryN is the row increment. If EveryN is 1, every row will be returned. If EveryN is 2, every other row will be returned, and so on. If you choose to use cell references instead of defined names, be sure to use absolute references, not relative references (e.g., $A$1 instead of A1).

=IF((ROW()-ROW(StartList))*EveryN>=(ROWS(Data)),"",
OFFSET(Data,(ROW()-ROW(StartList))*EveryN,0,1,1))

This formula will return values from Data starting at the first element in that range. If you want to start at a cell that is not the first cell of Data, use the following array formula.

=IF((ROW()-ROW(StartList))*EveryN>=(ROWS(OFFSET(Data,Start-1,0))),"",
OFFSET(OFFSET(Data,Start-1,0),(ROW()-ROW(StartList))*EveryN,0,1,1))

This formula uses an additional name Start which is the 1-based offset from the first cell of Data. If Start is equal to 1, values starting from the first row will be returned. If Start is greater than 1, the returned starting at that offset from the first cell of Data.

Summing Or Averaging Every Nth Row

We can write an Array Formula to sum up every Nth row from a column of numbers. The following formula will sum every Nth row in the range named Data. The names in this formula are the same and have the same meanings as described above.

=SUM(IF(MOD(ROW(OFFSET(Data,Start-1,0))-ROW(OFFSET(OFFSET
(Data,Start-1,0),0,0,1,1)),EveryN)=0,OFFSET(Data,Start-1,0),0))

A simple variation on this formula can be used to average every Nth row.

=AVERAGE(IF(MOD(ROW(OFFSET(Data,Start-1,0))-ROW(OFFSET(OFFSET(
Data,Start-1,0),0,0,1,1)),EveryN)=0,OFFSET(Data,Start-1,0),FALSE))

You can download a sample workbook that illustrates the formulas on this page.

This page last updated: 28-Oct-2008.