Given a set of values such as: 10, 12, 14, 16, 18 I would like to "estimate" that the next values will be: 20, 22, 24 and so on. I realize there are several different ways to extrapolate, and they will give different results. I'm assuming though that the number of options will be rather limited, so I'm not too picky about which method to use.
Google Sheets – How to Extrapolate Data
google sheets
Related Solutions
The following formula would yield the most elegant presentation.
Formula
=QUERY(DATA!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B")
Screenshot
This is a good alternative, but formula wise a bit different.
Formula
=QUERY(DATA!A:C, "SELECT A, B, SUM(C) WHERE A IS NOT NULL GROUP BY A, B
LABEL SUM(C) 'Costs'")
Screenshot
Example
I've added the results into your dummy sheet.
You can achieve the effect of CLAMP(value, min, max) with
=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)
which is compatible with ARRAYFORMULA and uses value only once. Explanation:
SQRT(value-min)^2+minreturns value if value>=min, and gives an error otherwise.IFERRORreturns the first argument, unless an error occurred in it, which case it returns the second- Thus,
IFERROR(SQRT(value-min)^2+min,min)clamps the value from below by min. - The other steps do a similar thing with max.
The above formula can be simplified, reducing the references to max and min from three to two of each:
=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))
Related Topic
- Google-sheets – How to dynamically retrieve and display data based on input from google forms
- Google-sheets – Which factor will give most speed up to every update to a large Google Sheets
- Google Sheets IMPORT – How to Reference IMPORT Function Result as Defined Name
- Google Sheets Performance – Identify Slowdowns
- Google Sheets – Aggregate and Extrapolate Irregular Data Over Time
- Google-sheets – How to run a google sheets query against a dynamic range
Best Answer
There are two ways I can think of:
Regression
With the following function, one can retrieve the linear regression coefficients of two sets of data:
The second set of data is the accompanying x-axis. This can be a normal numbering, as shown in the first sheet of the example file.
Once the regression factors are known, one can extrapolate any unknown x or y beyond the known set of data:
Since R2=1, the numbers given are exact values. I formatted the values to be rounded in the example, because the calculation shows floating point errors.
Dragging
Google has a way of predicting the outcome of a series, based on that series. If you drag the series like shown in the screenshot, then the outcome will appear automatically:
Example
I've created an example file for you: Extrapolate
A more practical example: https://webapps.stackexchange.com/a/35668/29140
Remark
The regression method will give you always a value, based on the factors. The more un-linear the sets of data are (R2<1), the more predicted they are.