# Moving Average

#### Dysprositos

##### Board Regular
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Andrew Poulsom

##### MrExcel MVP
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)

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

##### Board Regular
Beauty! I think i can make it from here,

thanks.

##### MrExcel MVP
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

##### Board Regular

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.

##### MrExcel MVP
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.

#### Sir Vili

##### Board Regular

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

Regards Vili.

##### MrExcel MVP
On 2002-11-03 07:29, Sir Vili wrote:

=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

##### Board Regular

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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,237
Messages
5,836,181
Members
430,406
Latest member
pmav

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back