+ Reply to Thread
Results 1 to 4 of 4

Best fitting curve

  1. #1
    ladee_bird
    Guest

    Best fitting curve

    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.


  2. #2
    TOM
    Guest

    Re: Best fitting curve

    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





    "ladee_bird" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  3. #3
    Christopher Merrill
    Guest

    Re: Best fitting curve

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  4. #4
    ladee_bird
    Guest

    Re: Best fitting curve

    Thank you both very much!


    "Christopher Merrill" wrote:

    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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.
    > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1