Google Sheets: aggregate and extrapolate irregular data over time

  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. it’s always interpolated between the neirest neighbours, fine with me…)

I am familiar with regular spreadsheet aggregate function, but I have no idea, what to use on such irregulary entered data. I have seen some stuff on extrapolition, but most was an sequences, (not a „sampled function“ if you will).

Sample Sheet

enter image description here

Thank you for your help 🙂

Go to Source
Author: Frank Nocke

How to filter out empty cells in Google Sheets With Query & Importrange

I am trying to use this formula but it’s not working. Do you hava an idea why?

QUERY(IMPORTRANGE(“IMPORTEDDATASHEET”;”06_20!M:AJ”);”SELECT SUM(Col17) WHERE Col1 = ‘Company Name’ AND Col23 <> ‘ ‘ label SUM(Col17) ””)
I want to filter by a specific company name in Col1 and filter out all empty cells in Col23 and then sum up col 17.

I also tried IS NOT NULL and != ‘ ‘ instead of <> ‘ ‘, it didn’t work either.

Go to Source
Author: N.K.