Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making Curves (w2kO2ksr1)

    Every now and then I run into a problem where I am modeling something and need to create a curved shape in the data in excel. Sometimes this is easy, but other times I find it very hard to dream up a formula that will cause my data to have a certain shape. I have the math background to do this, but it is not something I am required to do often, so I am always rusty on the techniques. Is there a reference work that would show how to create various shapes using excel formulae? I don't need help in the formula language (thanks to contributions from the folks on this board), but rather in techniques to create certain shapes. E.G. how do you set up a formula to create an "S" curve? Or asymptotically approach a maximum value? Or a bell curve? I can find all these things in physics and statistics books, but have never found a reference with excel specifics, so I am all the time trying to convert a math formula into a form that will work in excel. Any reference books out there?

  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: Making Curves (w2kO2ksr1)

    I don't have any reference books, but for S-curves, I like:
    =Bottom+(Top-Bottom)/(1+EXP((MidX-A1)*slope/2.7))
    Where bottom is Lower Asymptote
    Top is Upper Asymptote
    MidX is where the X value related to the Mid Y value [=(Top-Bottom)/2
    Slope is the slope of the mid section of the "S" curve
    A1 is your first X value

    Using the approximate values that you can Eyeball from your chart, calculate an ObservedY for each of your ActualX (you should also have a list of ActualYs)

    Then make the calculation
    =SUMPRODUCT((ObservedY-ActualY)^2)
    [Note this is the sum of squares calc, you want to minimize this to get the "least squares regression"]

    Tools- solver:
    Set target cell to the place you have the sumproduct calc
    Equal to "Min"
    By changing cells: [select: Bottom, Top, MidX, slope]
    Solve
    and you will get a least squares fit for your equation.

    You have to use solver since LINEST only solves "linear equations". This equation can NOT be linearized so you must solve with "non-linear regression". Note the solution is highly dependant on your starting guesses so you can get different results depending on where you start.

    Steve

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Cincinnati
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making Curves (w2kO2ksr1)

    Thanks, Steve

    That S-curve equation is exactly the kind of thing I need. I'm trying to model data that has never been collected, so I'm not really curve fitting, I'm curve designing.

    Bill

  4. #4
    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: Making Curves (w2kO2ksr1)

    In case it isn't obvious. This technique works for any equation (even the simple y = mx +[img]/forums/images/smilies/cool.gif[/img]
    Put the parameters for your equation in some cells
    Calculate the Observed values with the "guesses"
    Use the solver to calculate the parameters that minimizes the "sum of squares"

    Now the only problem is determining the equation

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making Curves (w2kO2ksr1)

    If you have to do this task frequently, I'd suggest to take a look at this product
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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