Hello,
Sorry to say but I studied this in class 20 years ago. Now it is gone. So I need help here.
I have 5-10 data points that I want to approximate a nonlinear polynominal to. I need to write the solution in excel, in the visual basic editor, meaning I want to use an iterative approach and not a "inverse matrix" solution. Can someone please help me here.
Ex)
x=2012, 2013, 2014, 2015, 2016
y= 5937, 5343, 4310, 3887, 0
Preferably, I want to have zero error at the boundaries but if the difference between the total least square error for "zero boundrary solution" and "without zero boundary error constraint" is too big I can skip the boundary conditions.
Would approciate if solution is written in a form that it is easy to see how I can code it in VB, flow chart maybe.....
Thanks in advance from an "old" student that has not been able to remember this knowledge 20 years later.....
/sbe
Hi,
Thanks for the quick reply.
No, I will seldom get an exact match since the data points will vary a lot. I want to be able to change the order of the polynomial if necessary.
I have been trying to use least square approach but everything on the internet has inverse matrix examples. I can not use that approach since the inverse sometime does not work, the matrix has no inverse.
Would be very grateful if you could help out. it is urgent to the stress level is not good
/sbe
Maybe the reason your system of equations isn't giving you an inverse matrix is because the matrix isn't square when you aren't fitting the polynomial exactly. The first step to getting a least-square solution is to first premultiply both sides by the transpose. This creates a square matrix, which will probably have an inverse.
As I understand it there does not always exists an inverse even for a square matrix. If I am wrong I need to find a way to code the inverse of a matrix. Before I need to determine the polynomial order based on number of data points. How do I do that?
1. CHOOSE the order of the polynomial you want. In this case, you can only have up to a quartic.
2. Write your system of equations that you get from the data points.
3. Write this system in matrix form .
4. Premultiply both sides by the transpose of , in other words, you should get . The matrix is now square.
5. IF the inverse of exists, premultiply both sides of the equation by it.
In other words .
Hello Prove It, and sbe70,
I have a similar problem. However I want to curve fit 3 points exactly resulting in some y=f(x) formula I can use to get any other point.
My sample data is x=1,120,2304 and y=1,145,210. I am actually more concerned with the middle data, and less so with the extremes.
I'm looking for a curve something like y = c * ln(x) + Y but that is just to get an idea: it does not have to involve ln(x).
Apparently Excel uses the least squares method for curve fitting, but when I selected a 2nd degree polynomial form for these 3 points I got the St. Louis Arch !
I guess then my last criterion would be a curve fitting method which also results in some miniumum area under the curve.
Thanks, Mike