Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 (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. #3
    3 Star Lounger
    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  5. #5
    3 Star Lounger
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  9. #9
    Platinum Lounger
    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 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
  •