Results 1 to 9 of 9
Thread: extrapoltion (Excel 2k, Win 2k)

20030926, 08:29 #1
 Join Date
 Oct 2001
 Location
 Newport, Gwent, Wales
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?
Thanks in advance
Ian

20030926, 09:01 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: extrapoltion (Excel 2k, Win 2k)
If your INdependent data is in the range A1:A5 (Xvalues) and your dependent data is in the range B1:B5 (Yvalues) AND you are fitting a LINEAR model, you can use:
You can predict (interpolation as well as extrapolation) a NEW yvalue 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 xvalue for a given Y as:
=(yINTERCEPT($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

20030926, 09:20 #3
 Join Date
 Oct 2001
 Location
 Newport, Gwent, Wales
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030926, 09:50 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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).

20030926, 10:50 #5
 Join Date
 Oct 2001
 Location
 Newport, Gwent, Wales
 Posts
 257
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20030926, 10:54 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: extrapoltion (Excel 2k, Win 2k)
Perhaps Edit > Fill > Series..., Growth option?

20030926, 11:18 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 nonlinear 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

20030926, 11:32 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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.

20030926, 14:57 #9
 Join Date
 Nov 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 5,016
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 addon might be of some use too:
Features least square fit of polynomial, logarithmic, exponential, fourier fitting.
Alan