Results 1 to 6 of 6

logarithmic equation: predicting values on a curve

This is a discussion on logarithmic equation: predicting values on a curve within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to predict values on a logarithmic curve based on a few known coordinates. For example, if ...

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    9

    Default logarithmic equation: predicting values on a curve

    Hi,

    I am trying to predict values on a logarithmic curve based on a few known coordinates.


    For example, if I had the following dataset, I would like to predict any of the values in between:
    1=1
    10=15
    25=32
    50=55
    75=78
    100=100

    I tried calculating a y=b*ln(x)+c equation with little luck. Any ideas to help guide my efforts?

  2. #2
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: logarithmic equation: predicting values on a curve

    You are talking about a "best fit" for a given data set. The most "scientific" way to calculate this "best fit" curve is based on the Least Squares Method (L.S.M.). Probably the best way to get acquainted with this method is to go to Google and do a search for either "best fit" or "Least Squares Method".

    I can tell you this, though. Any predicton is just a guess! If you are happy with a guess, then the L.S.M. will help you to get a recognized result. But, remember, it is still just a "guess".

    For instance, I had a friend who wanted to get compass readings in his boat and actual directions, and do a L.S.M. fitting to get a curve. I worked on this in QuickBASIC, came up with a beautiful program that lets you see all the data on an x-y graph, as well as any curve you choose, be it a straight line, a 2nd degree curve, a 3rd degree curve, etc., up to an nth degree curve, where n = number of data, up to 24, all based on the best fit curve, as predicted by the L.S.M. Well, he wasn't happy at all, because the only curve that passed through all points was the nth degree curve, and it was a very jumpy cure indeed, one that gave you no real predictablity at all!

    In fact, a hand-drawn curve usually does a better fit than a L.S.M. fit, in my experience.

    If you want to pursue this further, do e-mail me at:
    RAEsquivelC@Yahoo.com

    Regards,

    Ralph

  3. #3
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: logarithmic equation: predicting values on a curve

    O.K., I used my QuickBASIC Mathematics program, Curves4.qb, and got this "best fit curve": LOG10(y) = LOG10(x) + 0.069. Plot your values, and plot this curve, and tell us how you like it...

  4. #4
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    Hi!
    Its hard to make a very good curve to fit for random data.
    One best curve that will fit any given data is a spline.
    this algorithm gives good accuracy on values with in your given data. extrapolating is another story(guessing values outside your give data. But spline method of interpolation is far better than the LSM. I ahve posted a code sometime ago. If you are interested see link bellow.
    http://www.mrexcel.com/board2/viewto...77570&start=10
    There is always a better way!!

  5. #5
    Board Regular
    Join Date
    May 2003
    Location
    Katy, Texas
    Posts
    3,829

    Default Re: logarithmic equation: predicting values on a curve

    Well, I went ahead and used the Excel charting system, and got, for a straight-line fit: y = 0.9773x + 4.3197, almost y = x + 4.3197. I don't think you will find a better-fitting curve!

  6. #6
    New Member
    Join Date
    May 2004
    Posts
    9

    Default Re: logarithmic equation: predicting values on a curve

    Hi,

    Thanks Sixth Sense and RalphA for help guiding my efforts and pointing me in the right direction.

    I don't know if it's been discussed on this forum, but I ran across this GREAT addon for excel, which includes a spline function:

    http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm

    So far, I must say I am very impressed with spline and the XlXtrFun implementation. Incredible comes to mind. In the sample data set the curve could not be more perfect.

    The only caveat, and i believe it applies to all polynomial curves, is with forecasting. It's so sensitive, that the last few knots can heavily influence the projection, so in this regard, spline seems best for interpolation. For extrpolation of the datasets I've been using, a linear trend is more accuate. Having said that, spline will likely become a valuable part of my repertoire.

    Thanks again everyone!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com