# Thread: extrapoltion (Excel 2k, Win 2k)

1. ## extrapoltion (Excel 2k, Win 2k)

Dear All

I'm not sure if I'm losing my marbles here, but I remember being able to extrapolate data using Excel, I now want to do this again and can't find out how I did it before.......

Am I going barmy, or is there a way in Excel to extrapolate data?

Ian

2. ## Re: extrapoltion (Excel 2k, Win 2k)

If your INdependent data is in the range A1:A5 (X-values) and your dependent data is in the range B1:B5 (Y-values) AND you are fitting a LINEAR model, you can use:

You can predict (interpolation as well as extrapolation) a NEW y-value for a given X as:
=SLOPE(\$B\$1:\$B\$5,\$A\$1:\$A\$5)*x+INTERCEPT(\$B\$1:\$B\$5, \$A\$1:\$A\$5)

AND/OR You can predict (interpolation as well as extrapolation) a NEW x-value for a given Y as:
=(y-INTERCEPT(\$B\$1:\$B\$5,\$A\$1:\$A\$5))/SLOPE(\$B\$1:\$B\$5,\$A\$1:\$A\$5)

Change ranges as appropriate. You could also use LINEST if you have a linear equation of multiple INDEPENDENTS (including squared and cubed terms) to give the dependents.

You could also do linear transformations (ln, 1/x, 1/y) etc if desired.

Steve

3. ## Re: extrapoltion (Excel 2k, Win 2k)

Steve

The data isn't linear. It appears to be exponential from the chart I've produced of the data so far.

I think I must be losing my marbles as I remember it being an option within Excel. Maybe I had a none Microsoft add in back then, I was working for a different company and it was Excel 5 I was using....

Thanks

Ian

4. ## Re: extrapoltion (Excel 2k, Win 2k)

If you type extrapolate into the Answer Wizard, you should get an overview of possibilities. One of the functions you can use is GROWTH. This may be what you need - look it up in the help file.

You can extrapolate manually by selecting a range, then dragging the fill grip (the black square in the lower right hand corner of the selection) down with the right mouse button. When you release the mouse button, you will get a popup menu with several options, among which growth (exponential).

5. ## Re: extrapoltion (Excel 2k, Win 2k)

Hans

Thanks, sorted my problem nicely there. Stil convinced I used to a menu option for extrapolate in Excel 5 though. <img src=/S/pinkelefant.gif border=0 alt=pinkelefant width=20 height=20>

Ian

6. ## Re: extrapoltion (Excel 2k, Win 2k)

Perhaps Edit > Fill > Series..., Growth option?

7. ## Re: extrapoltion (Excel 2k, Win 2k)

Personally,
I tend to stick with Slope/intercept for 1 coef models that are "linearizable" or use Linest for the higher coefficient models. I understand better where the models are coming from and what the formulas are. WIth an equation I can plot my data and use the prediction to draw a smooth curve through the data. I also know how to calc the confi limits using the available data.

I have never investigated what function GROWTH uses or if I can get any stats from it (like Linest will give you).

For non-linear ones I tend to use solver which works pretty well to find a given equation and reduce the sum of the square of the deviations.

Steve

8. ## Re: extrapoltion (Excel 2k, Win 2k)

GROWTH is a simple function, it just extrapolates. It is comparable to TREND. The equivalent of LINEST for exponential growth is LOGEST. Which one you should use, depends on whether you need the statistics and how to interpret them.

9. ## Re: extrapoltion (Excel 2k, Win 2k)

If you think that the dependence is exponential, is it possible to generate extra columns that do have a linear dependence, then find the linear constants which would enable interpolation/ extrapolation on the line of best fit? Off the top of my head,
log(dependent var) vs. (independent var)
should be linear, at least for simple exponential dependencies.

This add-on might be of some use too:
Features least square fit of polynomial, logarithmic, exponential, fourier fitting.

Alan

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•