Home : Tip: Get the Average of the the last x values
Q14392 - INFO: Tip: Get the Average of the the last x values

Let's say I need to calculate the average of the last 5 values.  If I collect data everyday this is straight forward, simply use the grouping MOV5:

=GAVG(11,1,"MOV5")

What if we take samples only Monday, Wednesday, and Friday.  On Friday, I want to average Friday, Wednesday, Monday, last Friday, and last Wednesday.  Remember, the G functions (GAVG, GSUM, etc...) can take a start date and end date instead of an Offset and grouping.  Example:

GAVG(11,"9/2/2016","9/12/2016") returns the average from 9/2 to 9/12. 

So, how do we find the date of the 5th value before today?  Answer is DLV - Date of last value.  Set Startflag to a cell with the date you want to start searching on and set offset to 5:

=DLV(11,5,B15,30,"mm/dd/yy")

 

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 9/28/2016 4:46 PM.
Last Modified on 9/28/2016 4:46 PM.
Last Modified by Scott Dorner.
Article has been viewed 1769 times.
Rated 5 out of 10 based on 2 votes.
Print Article
Email Article