Moving Average

Dysprositos

Board Regular
Joined
Aug 3, 2002
Messages
92
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jul 11, 2002
Messages
148

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Jul 11, 2002
Messages
148

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Jul 11, 2002
Messages
148
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,077
Members
414,500
Latest member
kevdragon1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top