Google Sheets – Aggregate and Extrapolate Irregular Data Over Time

google sheetsgoogle-sheets-chartsgoogle-sheets-query

  1. I have a list in which I now-and-then (no fixed interval) enter the available budget.

  2. Most of all I want to extrapolate this to a future trend of the coming 10 years. (no rocket science, more of an average through line of the last 4-5 samples, as you would have in stock market charts or so)

Possible as an inbetween step I would like to aggregate this to one average number per quarter. As in most quarters there is no data available, it should mean interpolation. (or interpolation for all: so regarding Jan/April/Juli/Oct 1st. If it is always interpolated between the nearest neighbours… fine with me.)

I am familiar with the common aggregate functions, but I have no idea, what to use on such irregular entered data. I have seen some stuff on extrapolation, but that was monstly on sequences (not x⤇y).

Sample Sheet

enter image description here

Thank you for your help 🙂

Best Answer

Please, see Test file

Formula in cell C2 (with results in columns C:D) aggregates your data by quarters returning last date of respective quarter and average amount for this quarter.

=QUERY(
{B2:B,
ARRAYFORMULA(eomonth(date(year(EOMONTH(A2:A,0)),(quotient(MONTH(A2:A)-1,3)+1)*3,1),0))},
"Select Col2, AVG(Col1)
 Where Col1 > 0
 Group by Col2
 Order by Col2 Asc
 Label AVG(Col1) ''",0)

Columns E:F contain both actual data and projection based on given data. Projection is simple linear trend as you mentioned.

In particular formula in E2 returns quarters both known and projected, where

filter(C2:C,C2:C<>"")

includes quarters from initial data

and

arrayformula(eomonth(MAX(C2:C),SEQUENCE(40,1,3,3)))

takes last quarter in initail data and expands it for 10 more years (40 quarters - you can change it if needed)

Formula in F2 returns amounts both known and projected, where

={filter(D2:D,D2:D<>"");

includes amounts from initial data

and

TREND(
filter(D2:D,D2:D<>""),
filter(C2:C,C2:C<>""),
arrayformula(eomonth(MAX(C2:C),SEQUENCE(40,1,3,3))
)

returns linear projection based on initial data and expanded quarters for 10 years.

Related Topic