# How to increment cell references by 7 rows for each row dragged in Excel

In Excel 2007 and Google Sheets, I'm trying to drag a formula to populate cells in a column, but I need each successive row to have the cell references incremented by seven rows. For example, drag this formula:

``````=((E19-E12)+(F19-F12)+(G19-G12)+(H19-H12))*10,0)
``````

down to produce:

``````A22=((E19-E12)+(F19-F12)+(G19-G12)+(H19-H12))*10,0)
A23=((E26-E19)+(F26-F19)+(G26-G19)+(H26-H19))*10,0)
A24=((E33-E26)+(F33-F26)+(G33-G26)+(H33-H26))*10,0)
``````

and so on. Therefore increasing the row number by 7 each time. (daily readings added to weekly readings)

• …and your question is…? – Tetsujin Jan 21 '15 at 10:36
• What exactly do you want to achieve, dude? – duDE Jan 21 '15 at 10:39
• I am trying to find a way to drag this. I have to do this for 52 weeks over 4-5 different readings over those weeks. I was wondering if there is a way to 'automate' the 7 row jump on each line. Thank you for the help – Petros Jan 21 '15 at 11:08
• There are several existing questions about dragging a formula and having the cell references increment by 7 rows for every row you drag. See the discussion in the last few comments of this answer: superuser.com/a/859102/364367 for the gist of how to do it. Post back if you still have questions. – fixer1234 Jan 22 '15 at 0:13

## 1 Answer

It's not pretty, but using the `OFFSET` and `ROW` functions you can acheive what you are looking for

``````=(OFFSET(\$E\$19,7*(ROW(A22)-22),0)-OFFSET(\$E\$12,7*(ROW(A22)-22),0))+(OFFSET(\$F\$19,7*(ROW(A22)-22),0)-OFFSET(\$F\$12,7*(ROW(A22)-22),0))+(OFFSET(\$G\$19,7*(ROW(A22)-22),0)-OFFSET(\$G\$12,7*(ROW(A22)-22),0))+(OFFSET(\$H\$19,7*(ROW(A22)-22),0)-OFFSET(\$H\$12,7*(ROW(A22)-22),0))
``````

Paste this into cell A22 and drag down. You might consider putting a totals column in column I e.g. formula in cell I12 would be

``````=SUM(\$E12:\$H12)
``````

And also put these formulas in rows 19, 26, 33, etc.. (you can copy and paste that one down)

Then you could use a much simplified formula for weekly difference:

``````=OFFSET(\$I\$19,7*(ROW(A22)-22),0)-OFFSET(\$I\$12,7*(ROW(A22)-22),0)
``````