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:
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: