Best fitting curve

[ladee_bird]

Hi,
Maybe someone out there can help me!
I have a list of 500 experimental values. I also have a list of 500
predicted values which I predicted with an excel mathematical model I
generated. The excel model lets me change certain variable values which in
turn regenerates and gives me a new set of 500 numbers each time a variable
is changed. I wish to figure out which set of variables genertates a data set
which most closely matches my experimental data. At the moment I have the
data on numerous line graphs to visualise and some sets of variables look
very similar. How would I statistically evaluate these data sets to reveal
which is in fact the best fit?
Many thanks for reading this. I hope I've made it clear and understandable.

 

[TOM]

It depends on what kind of model you have. One of the simplest
measures is the least-squared-error. Find the difference between
each experimental value and the model's prediction for that value, square
that
difference, and them sum up the squared differences for all 500 of the data
pairs [measured, predicted] for each of your models. The model with the
smallest LSE is the best estimator.

If your model is linear, Excel has a built-in linear regression tool set.

-- Tom

 

[Christopher Merrill]

To exapnd a little on Tom's answer, I'll assume your experimental values are
in A1:A500 and the corresponding model values are in B1:B500. Sounds like
the model is nonlinear (if it is linear you only have to find slope and
intercept, use LINEST as Tom said).

You'll want to use Solver to minimize the sum of the square errors:

* Add a column of errors, =(A1-B1)^2 in C1:C500
* Set C501 = SUM(C1:C500).

To find the optimal model parameters, select Solver from the Tools menu, set
C501 as the target cell you want to *minimize*, add your model inputs as the
variables Solver should change, then press Solve.

 

[ladee_bird]

Thank you both very much!

To exapnd a little on Tom's answer, I'll assume your experimental values are
in A1:A500 and the corresponding model values are in B1:B500. Sounds like
the model is nonlinear (if it is linear you only have to find slope and
intercept, use LINEST as Tom said).

You'll want to use Solver to minimize the sum of the square errors:

* Add a column of errors, =(A1-B1)^2 in C1:C500
* Set C501 = SUM(C1:C500).

To find the optimal model parameters, select Solver from the Tools menu, set
C501 as the target cell you want to *minimize*, add your model inputs as the
variables Solver should change, then press Solve.