repeat a specific excel formula pattern down column

0
votes

I'm not an excel whiz but really stuck on this one and hoping a guru out there will be able to help me out as it's really important for some research I am doing - please!! I think it will be quite a simple fix (see attached example excel sheet for context below). If you are even able to modify the formula in question and re-attach that would be awesome too!!

There are 2 datasets in sheet 1 side-by-side. The data on left (rows A to K) displays data in 10 sec time epochs, the data on right (rows N to X) in 1 min time epochs. I would like to be able to drag the formula in P2 all the way down the column P based on data in column C (as per colour coding in red and blue).

You'll note that the P2 formula is taking a SUM of C2-C7 and P3 is taking sum of C8-C13. I would like to be able to continue this pattern down the column, perhaps with a better drag-down formula (or more efficiently – as there is loads of data!) if possible. Essentially I want each single data row on the right to move onto the next block of 6 rows from data on the left.

I hope I explained that well enough. Really hoping someone can help! Really important to me!

Patrick

See attached excel example - thanks so much!! I will be ever grateful!

https://www.dropbox.com/s/72r7ty9v15vzyyv/drag%20formula%20quick%20way%20-%20help.xlsx?dl=0

1
excelvba

1 Answers

2
votes

Here's one way. In P2 enter:

=SUM(OFFSET(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2),0,0,6))

and then copy that across and down as required. This is volatile due to the use of OFFSET which shouldn't be a problem with a small data set like this, but if your real data is bigger and you have more formulas, you may prefer:

=SUM(INDEX(C:C,(ROWS($A$1:$A1)-1)*6+2):INDEX(C:C,(ROWS($A$1:$A1)-1)*6+7))

which is only semi-volatile. (i.e. it will recalculate whenever the workbook is opened, even if its inputs haven't changed)