3 posts shown.
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 🙂
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.
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.