0

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)

  • 3
    …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
2

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.