Is Take() the best way to calculate a moving sum?

[JenniferMurphy]

About a week ago, Cubist introduced me to the Take function. I have made great use of it.

I just had a situation where I needed to calculate a moving average. The Take function seems like the perfect solution, but I would appreciate any comments.

Here are several examples. The mini-sheet is below.

Notice that I can't easily get the moving average from the moving sum without calculating the number of values in the sum, something that Take does for me.

Take Function.xlsx
	C	D	E
5	N=5
6	Value	Sum of Last N	Average of Last N
7	3	3	3.00
8	6	9	4.50
9	1	10	3.33
10	7	17	4.25
11	3	20	4.00
12	1	18	3.60
13	2	14	2.80
14	5	18	3.60
15	3	14	2.80
16	8	19	3.80
Moving Sum

Cell Formulas
Range	Formula
D7:D16	D7	=SUM(TAKE(OFFSET(Table2[[#Headers],[Value]],1,0):[@Value],-Num))
E7:E16	E7	=AVERAGE(TAKE(OFFSET(Table2[[#Headers],[Value]],1,0):[@Value],-Num))

Named Ranges
Name	Refers To	Cells
'Moving Sum'!Num	='Moving Sum'!$C$5	D7:E16

 

[Cubist]

For example N=5, do you count the first 4 as moving "5" averages/sums? There aren't enough 5 data points.

 

[JenniferMurphy]

For example N=5, do you count the first 4 as moving "5" averages/sums? There aren't enough 5 data points.

Isn't that clear from minisheet? The whole point of this thread is to show that the Take function adjusts the denominator for the averages to however many values there are.

Check the averages. When N=5, the average of the first row, 3, is 3. The average of the first 2 rows, 3, 6, is 4.5 (9/2). The average of the first 3 rows, 3,6,1, is 3.33 (10/3).

Here are the formulas:

 

[Cubist]

It's clear but I wasn't sure that was your intention. I don't think I would've done it differently. Maybe someone else might have other inputs.

 

[JenniferMurphy]

It's clear but I wasn't sure that was your intention. I don't think I would've done it differently. Maybe someone else might have other inputs.

I was going for a "moving" average, which I understand to be the average of the "last" N values. As values are added, the average moves along.

 

[KRice]

Have you considered a custom LAMBDA function to spill the averages? I suspected that something was readily available, and found a nice series of improvements/comments from @Jon Peltier at:

Improved Excel Lambda Moving Average

This follow-up to my earlier post 'Excel Lambda Moving Average' has simpler formulas and includes a moving average by date that deals nicely with missing values. https://peltiertech.com/improved-excel-lambda-moving-average/

peltiertech.com

Applied to your series of values, the single LAMBDA spills the results. Note that this uses Jon's 3rd offering, where, for row indexes that are too small, such that n points above are not yet available, the average is based on the number of points that are available,

Book1
	C		E
5	5
6	Value		Average of Last N
7	3		3.00
8	6		4.50
9	1		3.33
10	7		4.25
11	3		4.00
12	1		3.60
13	2		2.80
14	5		3.60
15	3		2.80
16	8		3.80
Moving Sum

Cell Formulas
Range	Formula
E7:E16	E7	=LAMBDA(datarange,numpoints,MAKEARRAY(ROWS(datarange),1,LAMBDA(r,c,LET(movingdatarange,DROP(TAKE(datarange,r),MAX(0,r-numpoints)),AVERAGE(movingdatarange)))))(Table2[Value],Num)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Num	='Moving Sum'!$C$5	E7

 

[JenniferMurphy]

Have you considered a custom LAMBDA function to spill the averages? I suspected that something was readily available, and found a nice series of improvements/comments from @Jon Peltier at:

Improved Excel Lambda Moving Average

This follow-up to my earlier post 'Excel Lambda Moving Average' has simpler formulas and includes a moving average by date that deals nicely with missing values. https://peltiertech.com/improved-excel-lambda-moving-average/

peltiertech.com

The Lambda function was suggested in another thread, but Take worked so well and it was easier for me to understand. I have Lambda on my todo list, but things tend to stay there for quite awhile. I'll take a look when I have a minute. Thanks

Do you think it is better than Take in this application? The formula looks more complicated to me.

 

[Cubist]

I was going for a "moving" average, which I understand to be the average of the "last" N values. As values are added, the average moves along.

Not exactly. The moving average of N length is defined by creating a series of averages of continuous subsets of length N of the full data set.
If you want to move 5 averages, you would need 5 data points. Else you're just calculating the simple average.

 

[KRice]

There are many way to tackle the moving average. I don't see any issues with the "TAKE" version, however, I tend to avoid OFFSET if it is practical due to its volatility. That may be more of a theoretical reason than a practical one, as I suspect your worksheet performance will not suffer noticeably (and I don't know what the performance penalty might be with LAMBDAs). Another option is to form the range to be taken dynamically using a different method (other than OFSET). But the most significant difference that would lead me to favor the LAMBDA is that the results spill from a single formula---no copying down the column...only one formula to enter/maintain. Also, the nature of the LAMBDA is that it's "code" is rather generic, making it easier to port to other applications. In this case, a copy of Jon's formula from the web site can be pasted directly into the worksheet, and then the two arguments (datarange and numpoints) are passed into it for the results.

The formula is somewhat more complicated, but some of that complexity is due to the syntax and behavior of LAMBDA functions. Once the formula is dissected, the individual components are fairly clear. For example, you'll find TAKE in there, in the calculation that forms the dynamic range of data to be used...for the variable named movingdatarange:

DROP(TAKE(datarange,r),MAX(0,r-numpoints))

Here we are taking the top r rows of the datarange, meaning that if we are on the 7th row index (where you have a value of 2 in the example), TAKE(datarange,r) creates the array {3;6;1;7;3;1;2}. Then notice this array is passed to the DROP function where we drop (eliminate) MAX(0,r-numpoints) elements of the array. So r is the row index (7) and numpoints is an input variable whose value is 5. So this expression becomes MAX(0,2), which means we'll drop the top 2 elements of the array, leaving {1;7;3;1;2} for the 5-element average in the next step...AVERAGE(movingdatarange). When r<=numpoints, the MAX expression results in 0, so no elements of the range would be dropped, which is what happens at the top of the data range before we've moved down n points.

 

[JenniferMurphy]

Not exactly. The moving average of N length is defined by creating a series of averages of continuous subsets of length N of the full data set.
If you want to move 5 averages, you would need 5 data points. Else you're just calculating the simple average.

We may be talking apples and oranges here. I am not trying to "move 5 averages". I'm not even sure what that means.

I am using the definition of a "simple moving average" as defined in several sources. Here are just two:

Moving Average (MA): Purpose, Uses, Formula, and Examples

A moving average (MA) is a technical analysis indicator that helps level price action by filtering out the noise from random price fluctuations.

www.investopedia.com

Moving average - Wikipedia

en.wikipedia.org

In my solution, a simple moving average of length N, is the simple average of the last N values. But if there are less than N values, then it's the average of as many values as there are. For the first N-1,values the moving average is the simple average of a many values as there are. As you can see in my examples, the moving average of the first value is just that value. The moving average of the first 2 values is the sum of those values divided by 2. And so on.

Now there are other types of moving averages, such as weighted and exponential. These take into account more values and they have different uses.