Explanation
In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed.
In Excel, it’s difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula is copied in 1-step increments across cells. However, with a little work it’s possible to construct formula references that follow specific patterns. In this example, we are using the OFFSET function , which is designed to create references to other cells, or cell ranges, based on a given starting point, or origin.
Starting at n
In the example shown, the formula in D5, copied down, is:
=OFFSET($B$5,ROW(D1)*3-1,0)
which copies every third value from the range B5:B59, starting at B7, which is the third cell in the range. The starting point inside the OFFSET function is the reference argument , provided as an absolute reference :
=OFFSET($B$5
The reference to B5 is locked so that it won’t change as the formula is copied down. The next argument is rows , which indicates the desired row offset from the starting reference. Rather than a typical hardcoded number, rows is provided as an expression that calculates the required offset:
ROW(D1)*3)-1 // calculate rows offset
This is where n is provided as 3, in order to copy every third value. Here, the ROW function is used to get the row number for cell D1. We start with D1 because we want to start with 1 for the first value. As the formula is copied down the column, the value returned by ROW increments by 1 because the reference to D1 is relative . This result from ROW is multiplied by n, which is what creates the “every nth” pattern, in this case, “every 3rd”. As the formula is copied down, the expression is evaluated like this:
ROW(D1)*3-1 // returns 2
ROW(D2)*3-1 // returns 5
ROW(D3)*3-1 // returns 8
These numbers may look odd to you in the context of “every 3rd value” but remember, this is an offset , starting with cell B5. The reason we subtract 1 is because the OFFSET function doesn’t include the reference cell when the rows argument is applied. In other words, offsetting by one row from A1 returns A2:
=OFFSET(A1,1,0) // returns A2
Subtracting 1 takes this behavior into account.
Finally, the columns argument is provided as zero (0), since we don’t want any column offset; we want to stay in column B. As the formula is copied down, it returns the required references:
=OFFSET($B$5,ROW(D1)*3-1,0) // returns B7
=OFFSET($B$5,ROW(D2)*3-1,0) // returns B10
=OFFSET($B$5,ROW(D3)*3-1,0) // returns B13
The number n can be changed as needed. For example, if n is changed to 5, the formula will pick up every 5th value.
Starting at 1
To start copying at the first row in a given range, then follow the “every nth” pattern afterward, you can adjust the formula like this:
=OFFSET($B$5,(ROW(A1)-1)*n,0)
In this version, we subtract 1 directly from the result from the ROW function. This “zeros out” the first instance of rows, so that OFFSET returns a reference to the current cell. The formula in cell F5 uses this approach:
=OFFSET($B$5,(ROW(F1)-1)*3,0)
The results can be seen in column F.
Explanation
The goal in this example is to calculate a cost of living (COLA) adjustment for each of the eleven years shown, starting on the second year. The actual adjustment percentage is entered in cell F6, which is the named range cola . Each year, the adjustment should be applied to the previous base amount in column C, starting with the base amount in cell C5.
The formula in cell C6, copied down is:
=C5+(C5*cola)
With 30,000 in C5 as shown, the formula is solved like this:
=C5+(C5*cola)
=30000+(30000*0.03)
=30000+900
=30900
As the formula is copied down the table, the relative reference C5 changes at each row, while the named range cola (F6) behaves like an absolute reference and does not change. The result on each new row is the previous year’s base plus the adjustment.
Total adjustments
To calculate the total of all adjustments, the formula in F7 is:
=SUM(C5:C15-C5)
This is an array formula , and must be entered with control+shift+enter, except in Excel 365 . The result is the total of all adjustments made since year 1.
Alternative layout
The screen below shows an alternative layout that breaks out the adjustment amount separately, and allows a different rate of adjustment for each year.
The formula in N5, copied down, calculates the adjustment:
=M5*L5
The formula in M6, copied down, calculates a new base for year:
=M5+N5
