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

If you have to do this task frequently, I'd suggest to take a look at this product

#### Posting Permissions

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