Results 1 to 5 of 5
Thread: Making Curves (w2kO2ksr1)

20020926, 12:04 #1
 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?

20020926, 16:02 #2
 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 Scurves, I like:
=Bottom+(TopBottom)/(1+EXP((MidXA1)*slope/2.7))
Where bottom is Lower Asymptote
Top is Upper Asymptote
MidX is where the X value related to the Mid Y value [=(TopBottom)/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((ObservedYActualY)^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 "nonlinear regression". Note the solution is highly dependant on your starting guesses so you can get different results depending on where you start.
Steve

20020926, 17:17 #3
 Join Date
 Mar 2002
 Location
 Cincinnati
 Posts
 36
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Making Curves (w2kO2ksr1)
Thanks, Steve
That Scurve 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

20020926, 17:38 #4
 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

20020927, 04:55 #5
 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.jkpads.com
Professional Office Developers Association