S-Shaped Trendline - Please Help

Hey all,

I'm trying to write a VBA code to be able to fit an S-Shaped trendline through a scatter plot in excel. The Curve must be of the form Y = a*atan((x/b)+c)+d.

I've tried estimating the A,B,C,D through Solver, but it takes too long and oftern doesn't find the best solution.

Do i need to go into writing my own VBA for solver, or is there a better way?

I've attached an example of what i'm working on.

Kind Regards

Sam

I can't look at your sample. A few general thoughts:

I've tried estimating the A,B,C,D through Solver, but it takes too long and oftern doesn't find the best solution.

I find that when Solver is taking too long and/or converging to the wrong solution, it is often because I gave it a bad set of initial values to start with. Do you have a good idea of what values a,b,c,d should be?

Sometimes on a problem like this, I've used LINEST to find the linear parameters (a,d) and then use Solver to find the non-linear parameters (b,c). This may or may not work better but it might be worth trying.

Do i need to go into writing my own VBA for solver, or is there a better way?

It's always possible that Solver's algorithms are not suitable for your problem. I've found that Solver's algorithms are pretty robust, but, like any numerical method, it does have some limitations. I wouldn't get too excited about writing your own code for this kind of problem, because it will be a major undertaking. Before I went that route, I'd search high and low and see if there was another program (maybe a dedicated stats package) that had a suitable set of algorithms built into it for finding parameters for these kinds of equations.