Excel formula copying help

Hey everyone.

I've run into a bit of a headache here:

So, I have a massive pile of data on my first Sheet1, and on my second sheet, I need every single cell to contain an average of a specific range from that first sheet. It looks like this:
The average of A1-A6 from Sheet1 goes into cell A1 on the second sheet,
The average of A7-A12 from Sheet1 goes into cell A2 on the second sheet,
Then the average of A13-A18 goes into A3, and so on.

Basically, every time the formula moves down one row, the data range needs to jump up by 6 cells.

The problem is that when I try to just drag the formula down like normal, Excel treats it like A1-A6, then A2-A7, then A3-A8... which is exactly what I *don't* want.

Is there a fast way to batch-copy or drag these formulas over for thousands of rows? Manually typing them out is just a total waste of time. 😉

I already tried looking through Help, but I haven't had any luck finding a solution.

I suspect a macro command might be the cleanest way to handle this, so you might want to do a little digging on Google...

Until you find a more permanent fix, you could try this workaround:
Since your calculation data in Sheet1 is organized in blocks of six rows, go to cell A1 in Sheet2 and enter this formula: (AVERAGE(Sheet1!A1:A6)). Once that's set, highlight cells A1 through A6 and drag the formula down the column.
By doing that, you'll end up with the result sitting in every sixth cell of column A. From there, all that's left is to scrub out the empty rows:
- Select all the data in column A
- a - F5 > special > Blanks
- With only the blank rows selected, choose Delete Sheet Rows

That should leave you with a clean, continuous list of data without any gaps.

Joshua Garcia31 said:I honestly think a macro command is the way to go here. Maybe try doing a quick Google search on it?

While you're getting settled, you might want to give this a shot:
So, if you've got your data set up in Sheet1 in blocks of six rows, here’s how you handle it on Sheet2. Just pop this formula into cell A1: (AVERAGE(Sheet1!A1:A6)). Once that's done, just highlight cells A1 through A6 and drag the formula down. Simple enough.
So, you finally got those results in every 6th cell of column A. Now you just need to clear out all those empty rows:
I'll mark all the data in column A.
a - F5 > special > Blanks
It looks like you only have the blank rows selected... You'll need to go to Sheet1, then hit a - F5 > special > Blanks to get them all at once. Once those are highlighted, just delete the rows.

That way, you’ve got all your data sitting right there in a single array, without any of those annoying empty rows getting in the way.

Thanks for the tip. I actually tried following that process before, but I didn't know about that trick with selecting the blanks, so I just gave up on it. Thanks again.

In Sheet2:
Code:

Then just drag it down. It’s vital that you start the formula in the first row—since it relies on the current row number to find its target—but if you have headers or some other clutter and need to start from row 2, simply swap out ROW()-1 for ROW()-2, and so on.

And you've also got that option where you can lock down a function by row or by column or even both at once... So like if you've got a number sitting in A1 that you need to multiply by everything from B1 down to B10, you'd just pop =A$1*B1 into cell C1 and drag it all the way down to C10... The result there is that it multiplies A1 by B2 in C2 and just keeps rolling through to 10... If you want the column to stay frozen so it doesn't shift from A over to B, you just use $A... or if you want to freeze both the row and the column so it always grabs that exact same number, you'd go with $A$1

Thanks for all the replies, everyone. I've been absolutely swamped at work lately—basically working from sunrise to sunset—so I haven't had a chance to really dig into this yet. At first glance, my attempt with =AVERAGE(OFFSET(Sheet1!$A$1;INDIRECT("Sheet1!" & ADDRESS(1+6*(ROW()-1);1))-1;0;6;1)) didn't quite hit the mark.

It's funny how you can think you actually know what you're doing with this software, and then suddenly... you find yourself on a forum like 🙂

It’s funny how you can go through life thinking you’ve actually mastered a piece of software, only to be suddenly confronted with... well, a community forum.

and the occasional direct message.