ExcelGetting a Better Curve fit?

  • #1
3,003
2
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:

Picture2-25.png
 

Attachments

Answers and Replies

  • #2
422
1
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.
 
  • #3
3,003
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.
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!
 
  • #4
422
1
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.
 
  • #5
fcs
4
0
Hi,

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:

http://www.miraibio.com/images/curve-saladsamurai.gif


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.

http://www.miraibio.com/images/curve-saladsamuraiv2.gif

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:
  • #6
6
0
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
http://zunzun.com
[email protected]
 
Last edited by a moderator:
  • #7
6
0
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
http://zunzun.com
[email protected]

[PLAIN]http://zunzun.com/images/temp1.png [Broken]
 
Last edited by a moderator:
  • #8
2
0
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.

Thanks.
 

Attachments

  • #9
6
0
The program peakfit might help: http://www.sigmaplot.com/products/peakfit/peakfit.php [Broken]

James
 
Last edited by a moderator:
  • #10
2
0
Thank you for your quick response.

Is it possible to do so in excel ?
 
  • #11
6
0
Is it possible to do so in excel ?
Not that I know of.

James
 
  • #12
3,003
2
Thank you for your quick response.

Is it possible to do so in excel ?
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.
 
  • #13
DrDu
Science Advisor
6,089
800
You certainly know the "Solver" in the "Extras" Menu?
 
  • #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
 

Related Threads on ExcelGetting a Better Curve fit?

  • Last Post
Replies
5
Views
3K
  • Last Post
Replies
1
Views
6K
  • Last Post
Replies
3
Views
779
  • Last Post
Replies
5
Views
43K
  • Last Post
Replies
0
Views
7K
Replies
5
Views
3K
  • Last Post
Replies
5
Views
2K
Replies
2
Views
7K
  • Last Post
Replies
8
Views
2K
Replies
1
Views
927
Top