ExcelGetting a Better Curve fit?

  • Thread starter Saladsamurai
  • Start date
  • Tags
    Curve Fit
In summary: B1, the cell that holds the RSME value, and it will provide you with a solution that fits the data perfectly. Hope that helps!
  • #1
Saladsamurai
3,020
7
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

  • EQ_Constants.xls
    54 KB · Views: 227
Physics news on Phys.org
  • #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.
 
  • #3
shoehorn said:
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
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
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
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
zunzun@zunzun.com
 
Last edited by a moderator:
  • #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
http://zunzun.com
zunzun@zunzun.com

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

Thanks.
 

Attachments

  • curves.JPG
    curves.JPG
    33 KB · Views: 501
  • #9
The program peakfit might help: http://www.sigmaplot.com/products/peakfit/peakfit.php [Broken]

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

Is it possible to do so in excel ?
 
  • #11
ripper_pc said:
Is it possible to do so in excel ?

Not that I know of.

James
 
  • #12
ripper_pc said:
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
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
 

1. How do I perform a curve fit in Excel?

To perform a curve fit in Excel, you can use the "Add Trendline" function under the "Chart Design" tab. Select the data points you want to fit the curve to, and choose the desired type of curve (e.g. linear, exponential, polynomial). The trendline will then be added to your graph, and you can adjust the parameters and display the equation for the curve.

2. What is the purpose of a curve fit in Excel?

A curve fit in Excel is used to find a mathematical relationship between a set of data points. It allows you to determine the best-fitting curve that represents the data and can be used to make predictions and analyze trends.

3. How can I improve the accuracy of my curve fit in Excel?

To improve the accuracy of your curve fit in Excel, you can try adjusting the type of curve you are using, or adding more data points to your graph. It may also be helpful to use a higher order polynomial or to transform your data if it is not linear.

4. Is it possible to manually adjust the curve fit in Excel?

Yes, you can manually adjust the curve fit in Excel by editing the trendline parameters. You can also add additional data points or use the "Add Trendline" function to try different types of curves.

5. Can I use a curve fit to make predictions in Excel?

Yes, a curve fit in Excel can be used to make predictions. Once you have determined the best-fitting curve for your data, you can use the equation of the curve to predict values for new data points. However, it is important to note that the accuracy of these predictions may vary depending on the quality of the curve fit and the assumptions made.

Similar threads

  • MATLAB, Maple, Mathematica, LaTeX
Replies
9
Views
1K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
4
Views
864
  • MATLAB, Maple, Mathematica, LaTeX
Replies
5
Views
2K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
14
Views
2K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
8
Views
2K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
9
Views
4K
  • MATLAB, Maple, Mathematica, LaTeX
Replies
4
Views
1K
Replies
16
Views
2K
  • Astronomy and Astrophysics
Replies
1
Views
1K
  • STEM Educators and Teaching
Replies
5
Views
542
Back
Top