CODEWITHSUNDEEP
exceloptimizationsyntaxsolver
kiriloff
kiriloff

Reputation: 26333

excel solver how to use - simple curve fitting

I am looking for a straightforward example of use of Excel solver.

I have Y data. I want to fit a model 'sum of two exponentials' to these data. How to write model? What is in set objective box, and in 'Changing values'? I am with Office 2010.

Thanks and regards.

Upvotes: 0

Views: 1460

Answers (1)

Dan
Dan

Reputation: 45752

It sounds like you want to use Solver to do least squares curve fitting?

Basic example:

Put your X-values in Col A, your Y-values in Col B (i.e. measurements / observations) then in Col C find Y values using your model. This model must include the parameters you are trying to find with solver. So for example =$G$1*exp(A1*$G$3) + $G$2*exp(A1*$G$4). This is a sum of two expoentials with 4 parameters. These model parameters are in cells G1-G4, they are what you are trying to solve. In Col D you want the square error of Y-model and Y-observed so =(B1-C1)^2. Then somewhere you need to sum column E. This sum is your objective cell. The changing values are the cells containing the parameters you used for finding your Y-Model values (i.e. G1-G4) which are the values you are trying to solve for. In this case you should be trying to minimize your objective function.

Upvotes: 1

Related Questions