Thread: Using one cell to specify the length of a moving average

Hi there,

Column A has the input data, column B has the output data.

I would like to specify the value of a moving average in cell B1 e.g. 20

I have tried the below which I think is right in terms of the numbers given.

=AVERAGE(OFFSET(A1,1,0,$B$1,1))

However, the numbers start in the wrong cell. I need the series to start 20 cells down. How can I get that solution? Ultimately, if I changed B1 to 50, the series would start 50 cells down and so forth.

I am sure this is a fairly simple problem, but I am somewhere near the bottom of the food chain in terms of Excel programming. I have googled this and also searched the forum (found it today and very that I did as I am sure I will be back for more) .

The below answer from Bryan Hessey does seem to do the job but I cant figure out what he did. If that is the only way, can someone walk me through what he is asking excel to do. I dont follow it at all.

http://www.excelforum.com/showthread...moving+average.

Any help/suggestions would be thoroughly welcome.

thanks,
Paul

Hi Paul,

IF you don't have any blanks, you can name a range as follows
MvgRange = OFFSET($C$3 (where your data begins),COUNTA($C:$C)-1-$B$1(assuming there's 1 header row and B1 is where you put the # of numbers you wish to include in your average e.g.10),,$B$1)

Code:

=OFFSET(Sheet1!$C$3,COUNTA(Sheet1!$C:$C)-Sheet1!$B$1-1,,Sheet1!$B$1)

Then in whatever cell you want the moving average to be

Code:

 =AVERAGE(MvgRange)

You could also plug that formula directly into your average formula but I prefer working with named ranges.

See attached sample

ChemistB

First of all, thank you for Bryan & ChemistB for your speedy replies - i'm amazed at both the timeliness and the content. However, I dont quite have the answer I need, but i can see it is on the near horizon.

I should have attached the file, mea culpa maxima. Here it is.

The column to update is F. As you can see the moving average is placed in F1. The moving average formula begins in F3 and is =AVERAGE(OFFSET(E2,1,0,F$1,1)). It continues all the way down the column.

The moving average series should start in F22, i.e. when the 20 day moving average begins. If I had put 25 in F1, the moving average should start in F27 and so forth. I want the moving average to start automatically in the right place, rather than inserting the requisite number of cells.

So that is the problem to resolve.

As an aside, Bryan, you provided 3 solutions in your MoveA spreadsheet in the thread I had referred to in my first post.

The third solution 'extra formula' does the calculation. This is the formula you used.

=IF(A12="","",AVERAGE(INDIRECT("A"&ROW()-C$1+1&":A"&ROW())))

I dont get why you use:

1. IF(A12 ="",""
2. ("A"&ROW()
3. -C$1+1&":A"&ROW

As I said, I am a bit green when it comes to excel, so if it is a noob question that I should just F1, then I will do that (although I cant find it in there at the moment!). Also, this is secondary to the problem in hand and is therefore not so important.

Thanks again
PS Si fractum non sit, noli id reficere. Now I know what it means, I quite agree.

Attachment ABB.zip

Not sure if that worked in the last post - I dont see it. Here it is again.

Okay, I see what you want now. I'll need to ponder this one for a while.

ChemistB

Okay, I'm sure that there are individuals in this forum who might be able to streamline this but here is the solution I came up with;

Starting in F2 (G2 if you want to test it next to the formula you already have)

Code:

=IF(ROW()-$F$1-2<0,"",AVERAGE(OFFSET($E$1,ROW()-$F$1,,$F$1)))

This formula puts blanks ("") in every row until it gets to where your data equals your criteria in F1,
Then the second term in the OFFSET it tells Excel where to start calculating the average from (ROW()-F1) where ROW() is the current row of the formula.
So for example where you set F1 = 20, in row 21, it simplifies to

Code:

=IF((-1)<0,"",AVERAGE(OFFSET($E$1,1,,20)))

which returns "" or blank.
At row 22, it simplifies to

Code:

=IF(0<0,"",AVERAGE(OFFSET($E$1,2,,20)))

which gives you the average from E3 to E22. Tada!

Let me know if I wasn't clear on something. Good luck!

ChemistB

Blimey, that's good. Here's my hat, i'm taking it off to you (& Bryan).

I'm now trying to decompose it (it's late after work, almost 9). But really that is just unreal. I figured out some new things there too, like the use of ROW(), ""... very basic stuff. I feel like an amoeba crawling out of the primeval ooze. Not sure if amoebas ever crawled, but there you go.

Basically, thanks guys. I'll be back for more no doubt.

Best,
Paul