Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Determine best-fit curve and its function [y=f(x)] from multiple (x, y) data points in Excel

Hello,

This is a combined math and Excel question.

Given multiple (x, y) data points in a spreadsheet, how do you determine the curve that most closely represents the data points as well as the algebraic function of that curve?

For example, the following screenshot shows a set of 12 data points plotted as a scattergram:

User generated image
In this particular case, the chart looks like the x-y relationship could be linear.  However, I assume a straight line function can be determined for any set of points no matter how dispersed.  Is that correct?  If so, does that mean there are two methods for determining best-fit curves, one linear and one nonlinear?

I'm familiar with the general linear equation (y = mx + b) and how to obtain it from two points but what method is used when more than two points are present (and they do not reside on a common line)?  Does it require integrals?

Also, does Excel have functions which provide solutions for the same questions?

Thanks

File attached
x-y-data-points_EE-2017-04-04.xlsm
SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Thanks a bunch for the great and varied responses.  I wish I could award 500 points to each contributor.
You're welcome and I'm glad I was able to help you keep your head above the water:)

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016