Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

ExcelGetting a Better Curve fit?

  1. May 31, 2010 #1
    Hey folks,

    Am I doing something wrong here? It just seems like I should be able to get a better fit. The data is plotted for the H2-->2H reaction data. I have attached the .xls file. Don't worry, no macros :wink: I created it on a Mac, but hopefully everything converts correctly.

    Here's a snapshot:


    Attached Files:

  2. jcsd
  3. May 31, 2010 #2
    Am I missing something very simple here? The title on your graph says "Temp versus K" but your axes are actually log(K) versus T.
  4. May 31, 2010 #3
    No, you are not missing anything :smile: It is indeed log(K) and T. I have been messing around with the data, but forgot to change the title.

    Good catch!
  5. May 31, 2010 #4
    Are you sure there's a logarithmic dependency in your data set? If there was, then rescaling the x-axis to a log scale should give you a straight-line plot.
  6. Jun 1, 2010 #5


    User Avatar


    I used my company's http://www.miraibio.com/masterplex-readerfit/curve-fitting-for-plate-readers.html" and used the Best Fit feature (and optimized for root mean square error) to find the best model equation and it appears to fit the 4 Parameter Logistic or 4PL surprisingly well:


    I just realized that your x-axis was not in log scale. Here is the same curve fit with the x-axis on a linear scale.


    Here are the parameters that were calculated:

    A = -1862.64705 (lower asymptote)
    B = 1.04715 (Hill slope)
    C = 34.42647 (inflection point)
    D = 13.67627 (upper asymptote)

    R-square = 0.9999544 (although R-square is not really a really good indicator of nonlinear curves I thought I would post it anyway since you have it in your post)
    RMSE = 0.28340 (root mean square error is more reliable and it is really good in your case)

    Although MasterPlex ReaderFit is intended for ELISA analysis, it's application is flexible which is why I gave it a shot in your case. In all honesty, I was surprised at how nicely the curve fit came out to be (and I am assisting customers all day with curve fitting)!

    Anyway, I hope this information helps :smile:
    Last edited by a moderator: Apr 25, 2017
  7. Jun 16, 2010 #6
    You might consider:

    Ln(Kp) = a / (c * Temp + d)^b + Offset

    a = -1.4895890519700544E+04
    b = 9.6571085998524553E-01
    c = 3.3096509207808700E-01
    d = -1.4626098473438096E+00
    Offset = 1.5342375844418289E+01

    as it fits with RMSE of 0.0061 and r-squared of 0.99999997, the fitting page is at:

    http://zunzun.com/Equation/2/Miscellaneous/Polytrope%20Transform%20With%20Offset/ [Broken]

    It appears to interpolate and extrapolate smoothly and reasonably:

    [PLAIN]http://zunzun.com/images/temp1.png [Broken]

    I found this using the http://zunzun.com "Function Finder" at:

    http://zunzun.com/FunctionFinder/2/ [Broken]

    and turning off the Polyfunctionals as I wanted named equations only.

    James Phillips
    Last edited by a moderator: May 4, 2017
  8. Jun 16, 2010 #7
    This function might be better since it has only three coefficients, and the terms are simple functions.
    RMSE = 0.0083, R-squared: 0.99999995,

    y = a( Temp(K) ) + b( 1/Temp(K) ) + c( ln(Temp(K)) )

    a = -2.0624423802678490E-04
    b = -5.1874919532864478E+04
    c = 1.7768380294902535E+00

    James Phillips

    [PLAIN]http://zunzun.com/images/temp1.png [Broken]
    Last edited by a moderator: May 4, 2017
  9. Sep 23, 2010 #8
    Hello everyone, I am facing a similar problem,

    I have two curves, (assume data and model)

    data is derived from measurements taken by an Ellipsometry instrument,curve.

    model is derived from many many complex number calculations in excel,

    as you can see in the picture attached, they fit, but not that well,
    I have about 6 parameters that I can vary, all withing their certain limits.

    How do I implement this so as to get the set of values to get the best fit possible ?

    Any help would be greatly appreciated.

    I cannot share with you the xls file due to the confidential nature of the data.
    Let me know if you can help with the information I have already provided.


    Attached Files:

  10. Sep 23, 2010 #9
    The program peakfit might help: http://www.sigmaplot.com/products/peakfit/peakfit.php [Broken]

    Last edited by a moderator: May 4, 2017
  11. Sep 24, 2010 #10
    Thank you for your quick response.

    Is it possible to do so in excel ?
  12. Sep 24, 2010 #11
    Not that I know of.

  13. Sep 24, 2010 #12
    I think that the only way to something like this in excel is if you can guess the form that the function takes (which can be quite difficult). For example, you could guess that your funtion takes the form y = A*x^2 + B*x + C (which, of course, it does not) and then use your x values, along with some assumed values of A,B,C, to generate a set of corresponding y values.

    Now you can have A,B, and C located in some cells, say A1,A2,A3. And you would also have your original y data in a column and your new guessed function's y data in another. You can quantify the overall error between the original and new using the RSME function. Place that in a cell some where, say B1.

    Now use the Excel solver function. This allows you to minimize a value in one cell by adjusting values in other cells. You would use this to minimize the RMSE in cell B1 by adjusting the values of the constants in A1,A2 and A3.

    Again, this all hinges on you being able to guess an appropriate form of the function that fits your data, which I do not think I can help you with.
  14. Sep 24, 2010 #13


    User Avatar
    Science Advisor

    You certainly know the "Solver" in the "Extras" Menu?
  15. Apr 18, 2012 #14
    Hi, I have recently downloaded a software program that can perform the fit you want in excel. It can be downloaded at www.ivdtools.com
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook