Using one cell to specify the length of a moving average

Paulthenovice · #1 12-03-2007, 06:37 AM

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

ChemistB · #2 12-03-2007, 11:23 AM

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)

Bryan Hessey · #3 12-03-2007, 05:57 PM

Quote:

Originally Posted by Paulthenovice

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,

ChemistB has provided you with a solution, but to answer the question posed, the Offset is startpoint-rows-colums-rowcount-columncount, where A1 is always the top left corner, a useful known point.

To start at a different Row or Column try A1,21,0,$B$1,0 as the starting point.

For what you are asking, you are using B1 to indicate the number of rows, so try A1,$C$1,0,$B$1,0 with the start point in C1

From this you can start to understand the Offset as you are trying to use it, but if you still have problems please reply and show the formula as you have progressed it.

Bryan

Paulthenovice · #4 12-04-2007, 08:36 AM

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.

Paulthenovice · #5 12-04-2007, 08:44 AM

Attachment ABB.zip

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

ChemistB · #6 12-04-2007, 10:22 AM

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

ChemistB

ChemistB · #7 12-04-2007, 11:01 AM

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)))

Paulthenovice · #8 12-04-2007, 02:54 PM

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

Welcome to the Excel Forum

Forum Statistics