Make data range in formula shift for a whole range when copying cell

Hello Zaron,

Welcome to the forum

To shortly answer your question, yes, this can be done either by VBA (average by every 5 rows) or purely formula through the manipulation of ROW() and INDIRECT, for e.g, in

B1:

Please Login or Register  to view this content.

And it will calculate the Average of A1 to A5.

And when you drag it to B2, it will calculate the Average of A6 to A10

Idea-wise, (ROW()-1)*5+1 on Row 1 is (1 - 1) * 5 + 1 which will be 1, on row 2 will be (2 - 1) * 5 + 1, which is 6, so on and so forth

My question is, is there any easy way or trick to make the data range in the formula to shift for the whole range, not just shifting 1 cell, when copied? In this example it means B2 = AVERAGE(A6:A10) after copied.

what would be the basis of the "jump"? The formula in post 2 may well work if the range is 5, but I doubt that your requirement is that simple. Can you fill in the details of exactly what you are trying to do?

Another way similar to Lemice's. Must be array entered. With data in column A array enter this in B1 and fill down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Thank you all very much for your quick reply. I didn't expect to receive answer so soon.

My intention is, just as Lemice said, to calculate an average of A column every n rows. My work requires calculation like that frequently. The ranges are usually every 9 - 15 rows, and most of the time the whole data consist of more than 200 rows, which make rewriting "AVERAGE" formula, scrolling down and manually selecting the range quite annoying. The best and fastest method so far I thought of is to copy and paste the formula at interval similar to the range I want to calculate average (as in my example I would paste the "AVERAGE" formula at cell B1, B4, B7), then drag them together (as dragging does not change the formula). There is risk to mispaste though (like pasting at B3 in stead of B4), which is not easy to track and can throw off all your subsequent calculation if you are not careful.
I am still a beginner to excel, so this is the first time I saw functions like "ROW" and "INDIRECT". I looked them up, but am still quite confused about how to use them. I also don't understand how to incorporate functions into the range of "AVERAGE" function. I used the key word "average by every 5 rows" suggested by Lemice to search google and saw some people suggesting OFFSET formulas. Still, I couldn't draw out the principle of it. My data usually have some heading rows, and start at A3 rather than A1, so I couldn't test your suggested formulas. I tried to modify the formulas but couldn't, as I still don't understand the principle. I hope you would be kind enough to explain them again to me.

Since most of Excel Formula requires a range, for example

=AVERAGE(Range to average)

Sometime it is hard to pass customized range into it, for example, A1:A5 in the formula in row 3, but A6:A10 in the row below, row 4.
And in cases like that, functions such as OFFSET (Where you "Shift" the range by certain rows, columns, basing on calculation, for example, for every row below the first formula, you shift x amount of rows down, y amount of columns to the left / right), or a more powerful one in my opinion, INDIRECT (Where you construct the range directly and pass it to the formula).

For example, starting from row 3, B3, you want to calculate the average of every 5 cells, so in Row3, it's A3:A7, in Row4, it's A8:A12, so on and so forth.

First, you will have to find a formula that, for every row down, it gives you a number that's 5 times that value (Since we are averaging 5 values at a time, right?) - which I immediately think of ROW(), the formula that returns the row number of the row it's on.

So I wanted to return a value of 3 on Row3, 8 on Row4, and 13 on Row5, thus I use
=(ROW()-ROW($B$3))*5+ROW($B$3)

On Row3, ROW() returns 3, ROW($B$3) returns 3, so the equation returns (3 - 3)*5 + 3 = 0 * 5 + 3 = 0 + 3 = 3, perfect
On Row4, ROW() returns a 4, ROW($B$3) returns a 3 (the row number of cell B3), so the equation returns (4 - 3) * 5 + 3 = 1 * 5 + 3 = 8
On Row5, ROW() returns a 45 ROW($B$3) returns a 3 (the row number of cell B3), so the equation returns (5 - 3) * 5 + 3 = 2 * 5 + 3 = 13

And you get the idea. You might wonder about ROW($B$3) - The very first row that the formula starts is 3, thus $B$3 (The $ sign is to prevent the reference from changing when you drag the formula left / right) - So if you want the formula to start on row 5 instead, change $B$3 to $B$5
What about when you want to calculate every 7 rows instead? Simply change *5 to *7

And right now, you can use the OFFSET formula like this

Please Login or Register  to view this content.

And it will return the average of 5 rows, depending on the row it is on. Here's why:

The format of OFFSET is OFFSET(range, rows, column) (there are 2 more at the end, optional, with are height and width, you can look them up later)
So let's say you wanna calculate every 5 rows, that's perfect, let's set our original range to A1:A5, and remember the ROW() formula above giving the value 3 in the 3rd row? What would happen if you shift the range A1:A5 3 rows down, 0 column?
It will become A4:A8.
How about only shift it 2 rows down? A3:A7 - perfect (hence the -1 you see in the formula)

What about INDIRECT? In order to use INDIRECT, you have to construct and put together the range as text string - from letter to letter, so in order to put together a text like "A3:A8" this is what I do
"A" & (ROW()-ROW($B$3))*5+ROW($B$3) & ":A" & (ROW()-ROW($B$3))*5+ROW($B$3)+4

On Row3, this formula returns:
"A" & 3 & ":A" & 3+4 = "A3:A7"

And to put it into AVERAGE:

Please Login or Register  to view this content.

I'm sorry I'm bad at this, but I tried, and if you have any question, don't hestitate to ask me ok xd

If your Data starts at A3, from Column A to D for example, and you wanna see how the parts of the formula is working, in these cells, paste these formula in:

E3:

Please Login or Register  to view this content.

F3:

Please Login or Register  to view this content.

G3:

Please Login or Register  to view this content.

H3:

Please Login or Register  to view this content.

And drag them down until you start seeing #DIV/0 - You run out of range to calculate (Excel please, still can't average a bunch of 0)

Well, if you are lazy to construct such formulas, you can always paste this formula in row 3

Please Login or Register  to view this content.

(yes, ROW() again!)
and drag it down, copy paste as value, Ctrl - H, Find all "Nope", Replace all with true blank, Ctrl - G, Special, Go to blanks, right click --> Delete, shift cells up.

It works too, but yeah, it's a hassle, and I'd just make a formula, paste it in the note or something, and paste it into workbook that needs it, then edit it to fit the data range xd

(Or you can use the above formula, then make ... Array formula to return to you a list of not "Nope" - I admit, I'm that bored xd)

Anyway, thank you for the feedback, and if you have found a fitting solution to your problem, please mark the Thread as [SOLVED] using the Thread tools right above post #1. It keeps things neat and tidy.

Don't hesitate if you have any other question.

And, have a great day.