• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Best fit curve in Excel?

B

BigToque

Lifer
I'm doing a lab in physics, and the goal of the experiment was to map out a set of coordinates and use the data to generate equipotential lines.

I've got all my data into Excel, but I don't understand how to make a smooth lines of best fit for each of the lines. Google has been completely useless.

This is what it looks like now...

data.jpg
 
Yeah, I don't like how excel doesn't allow custom trend lines :\, the best way would probably be to have you're data as just the scatter points, and include another data series as the expected values, except changing that series' chart type (right click change series chart type) to a scatter with smooth lines (without the data point markers).
that only works if you know the expected values though and input enough data points
 
Last edited:
No labels on the axes?! No title on the graph?!

I was going to suggest Graphical Analysis; I think you can download a free full function demo (good for 30 days.) I've yet to find a student who prefers using Excel to Graphical Analysis for plotting data, linearization of data, and best fit curves.

Unfortunately, it won't do the type of curve fitting that you're looking for. It's pretty easy to do that in Excel though -
Chart Wizard
XY Scatter
Select the option with smooth curves.
 
Edit: I'm not sure if you mean a smooth curve through your data points, or if you're looking for a best fit curve for the data?

If you're looking for a best fit curve, your data suggests parabolas in the form x=y^2

My personal preference, that does away with all the hocus pocus, is to linearize the data. I don't know what you have for the x-column and y-column, but since it looks like x=y^2, then use a formula and make another column for y^2, then plot x vs. y^2

Another option, use a logarithmic scale on both axes, then a best fit line.
 
Gigantopithecus said:
Positive values on the x axis to the left of the y axis, too!
Click to expand...
Yeah, wtf? I just noticed that.

Another thing - they ARE functions. Looking at your data, most of the values on the y-axis are all at integer values. I find it very unlikely that the dependent data would take on such values, while your independent data consisted of decimal values.

Looks like major graphing problems!

edit: wait a second.. A lot of both the independent AND dependent data are at "nice" values? What's up with that??
 
Last edited:
If you have access to Matlab, use the curve fitting toolbox. If you want to know how curve fitting works.. Read a peer reviewed paper.
 
Have a look at:
http://surfit.sourceforge.net/

Creating equipotential lines is a much different problem than trying to fit a line through a set of points. Computer contouring is usually done in two stages, the first is to interpolate/extrapolate a grid of values based on your data points generally using a least squares fit method or kriging. The second is to draw the contour lines based on the grid created in the first step.
 
Last edited:
Use XY Scatter plot.
Add trendline.
Set trendline to polynomial, increase the order until it looks good. (your data is not exponential)
Set the Display Equation on Chart radio box to get the equation.
 
Last edited:
It is possible to fit a custom function in Excel, but it takes more than a couple of clicks. You need to make a column with the model predicted values. Make another column of squared residuals (data-model prediction at each point). Then, use the solver add-in to minimize the sum of squared residuals by changing the model parameters.

The reason none of the "trendlines" fit your data is that they are not single-valued functions in their current form - each value of x has multiple values of y. You can flip the axes and fit each line then. You could also take advantage of symmetry about y=8 and only fit the points above this, then flip it. The problem here is that the fitted function may not have the necessary properties (i.e. it may not have symmetric properties) unless you choose an appropriate curve type.

The best approach is probably to make a contour plot, but this is only straightforward in Excel if you have your data in certain forms.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top