How to force fixed points in polynomial fitting

[jactex]

I am fitting my data with a polynomial in Excel. I want to force the fit function to go through the first point and last point of my data.

[Richard Buttrey]

Not quite sure what you mean. A workbook with some data and knowing what order of polynomial you want would be a good start
If you upload it we may be able to help.

[MrShorty]

Unless you've already done the algebra, I find that this is more of an algebra exercise than a programming exercise.

Regression equation: y=p(x) [p(x) is your polynomial]
point1: y1=p(x1) solve for one of the polynomial coefficients
point2: y2=p(x2) solve for a different coefficient
substitute those expressions back into p(x) to get a new polynomial along with new expressions for y and x.
Perform the regression on the transformed polynomial.

I find that the algebra part is usually the hard part, and Excel is no help with algebra. Once you have the transformed values for x and y and the transformed polynomial, the regression part is usually straightforward.

As Richard says, help us understand the details and what specifically you don't know how to do, and we may be able to help.

[curiouscat408]

@jactex.... Mathematically, in general, I believe we can expect an exact fit only when the number of data points to fit (n+1) is one more than the "degree" of the polynomial (highest power n).

In that case, we can use Excel LINEST (VBA WorksheetFunction.LinEst) to determine the coefficients, within computational limitations.

This can be demonstrated with the example below. See the attached image and Excel file.

Note that the columns "x wgt" (read: x weight) and "y wgt" are used just for creating the example. They are not needed for the LINEST solution.

Likewise, the formulas in the columns labeled "x" and "y" are used just for creating the example. You might substitute actual data.

The following LINEST formula is array-entered in A12:G12 (for a 6-degree polynomial):

=LINEST(B2:B8, A2:A8^{1,2,3,4,5,6})

Then the following SERIESSUM formula can be used to generate the "est y" (read: estimated y) values in column C:

=SERIESSUM(A2, 6, -1, $A$12:$F$12) + $G$12

G12 is not included in the SERIESSUM range, just in case it is zero (unlikely).

The following formula in the column labeled "est y err" is relative error between "y" and "est y" values:

=C2/B2 - 1

Press f9 to generate new examples.

Note: LINEST can handle polynomials with degree higher than 6. But the XY Scatter chart trendline is limited to degree 6.

Caveat: LINEST can generate very eccentric curves. Other methods might generate more reasonable curves consistently. But I do not believe they are built-in functions in Excel.


-----