Moving Average

[Dysprositos]

Hello all,

Im trying to return an average of the last three values in a list. The list grows as more values are added to the bottom. But id like the average to "move" as more values are added.

So far i can return the last value in the list by
=LOOKUP(9.99999999E+54,Sheet1!T:T)

But how can i add this value to the two previous values?

TIA

 

[Andrew Poulsom]

This is one way:

1. Select the cell that contains the average formula (say A10) and choose Insert, Name, Define from the menu.

2. In the Name box type UP1 and in the Refers to box type:

=OFFSET(Sheet1!$A$10,-1,0)

and click Add.
3. In the Name box type UP3 and in the Refers to box type:

=OFFSET(Sheet1!$A$10,-3,0)

and click Close.

4. In cell A10 type:

=AVERAGE(UP1:UP3)

 

[Dysprositos]

Beauty! I think i can make it from here,

thanks.

 

[Aladin Akyurek]

On 2002-10-31 02:37, Dysprositos wrote:
Hello all,

Im trying to return an average of the last three values in a list. The list grows as more values are added to the bottom. But id like the average to "move" as more values are added.

So far i can return the last value in the list by
=LOOKUP(9.99999999E+54,Sheet1!T:T)

But how can i add this value to the two previous values?

TIA

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter the following in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

Now use the following to compute a dynamic average of the last 3 values in column T...

=(SUM(T:T)-SUM(T1:INDEX(T:T,MATCH(BigNum,T:T)-3)))/3


BTW, you can retrieve the last numeric value from column T using...

=LOOKUP(BigNum,T:T)

Note. Prefix T:T with Sheet1!.
This message was edited by Aladin Akyurek on 2002-10-31 03:19

 

[Sir Vili]

Hi Dysprositos.

My solution, using formula in U1 and taking values from Column T, is this:

=SUM((OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-3,0 )+OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-2,0 )+OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-1,0 )))/3

Regards Sir Vili.

 

[Aladin Akyurek]

On 2002-10-31 04:00, Sir Vili wrote:
Hi Dysprositos.

My solution, using formula in U1 and taking values from Column T, is this:

=SUM((OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-3,0 )+OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-2,0 )+OFFSET(Sheet1!T1,COUNTA(Sheet1!T:T)-1,0 )))/3

Regards Sir Vili.

Three times OFFSET, which is a volatile function? Adopt mine, Sir, which is fastest and non-volatile.

Aladin

 

[Sir Vili]

Hi Aladin.

Thank You for Your Comments. I always apperiate them highly.

One variation to calculate and still using Offset - function, what about this:

=AVERAGE(OFFSET($T$1,MATCH(MAX(T:T),T:T)-CELL("Row",$T$1),0,3,1))

Regards Vili.

 

[Aladin Akyurek]

On 2002-11-03 07:29, Sir Vili wrote:
Hi Aladin.

Thank You for Your Comments. I always apperiate them highly.

One variation to calculate and still using Offset - function, what about this:

=AVERAGE(OFFSET($T$1,MATCH(MAX(T:T),T:T)-CELL("Row",$T$1),0,3,1))

Regards Vili.

That does not compute what Dysprositos is asking for.

Regarding various methods of averaging last N values and their relative efficiency, see my contrib in...

http://216.92.17.166/board/viewtopic.php?topic=18324&forum=2

 

[Sir Vili]

Hi Aladin.

Thank You again for Your Comments. I found them extremely valuables.

1. Yes You are right (as always) formula did not calculate correct. I did not test it enough in right way.

2. Very interesting link that You gave to be checked.

3. Let us try one formula more:

=AVERAGE(OFFSET($T$1,LARGE(ROW(1:65535)*($T$1:$T$65535<>""),4),0,LARGE(ROW(1:65535)*($T$1:$T$65535<>""),1)))

Formula as array entered. Please notice that I did not gave 65536, but 65535. When changing 65535 to 65536, formula do not calculate. I do not know if it efficient formula or not, but about me it seems to work (I hope).

Best regards Sir Vili.