Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Can I extrapolate data in Google spreadsheets?

Given a set of values 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 to picky about which method is used.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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:

LINEST(y-axis,x-axis) ==> y=ax+b

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:

  • known x: ax+b
  • known y: (-b+y)/x

Since R2=1, the numbers given are exact values. I formated 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 serie, based on that serie. If you drag the serie like shown in the screenshot, then the outcome will appear automatically: enter image description here
enter image description here

Example

I've created an example file for you: Extrapolate
A more practical example: http://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.

share|improve this answer
    
Thank you, I'll give it a try. I'm aware of the "dragging" example, and I realize from my simple example that could seem as a viable solution, but I'm working with more complex data in my real sheet. :) –  user50849 Aug 26 '13 at 20:26
    
@user50849 See the second reference, under example, for a more mixed range. Was my answer helpfull? –  Jacob Jan Tuinstra Sep 7 '13 at 10:00
1  
It was indeed, and it solved my problem. Accepted the answer :) –  user50849 Sep 7 '13 at 10:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.