Average per day over a sliding window in calc

[cube]

I have a sheet with two columns, one is dates, the second is a numerical value (eg. log of money spent). The dates have arbitrarily large gaps between them, there might be duplicated dates (eg buying something twice in a single day). I want to calculate average value per day in a range centered around each record ± 7days.

pseudocode of what i want to do for each row:

• range start = find first row with date >= current date - 7days
• range end = find last row with date <= current date + 7 days
• range length = date diff(range end - range start)
• range sum = value sum(range start : range end)
• result = range sum / range length

I have a vague idea that this should be possible to do using some monster expression involving vlookup several times, but I never had the guts to actually try it

It seems to me that this should be fairly common (I have sort of needed this several times already), but I can’t figure out a simple way to implement a filter like this in calc.

Is there a trick I’m overlooking?

[anon73440385]

Hello,

may be you could use the formula in column C of the following sample file:

Floating-Average.ods

The formula is: =SUMPRODUCT((A:A>=A2-7)*(A:A<=A2+7);B:B)/SUMPRODUCT((A:A>=A2-7)*(A:A<=A2+7)) (for date in cell A2)

Note(s)

• Columns D through G are only there to illustrate how the formula in column C works. They are not required for the solution
• There may be a more performant solution.

Hope that helps.