-1

I need help with calculation of polynomial approximation function with intercept at 0, 0 point. I have few points in Excel like this: Point with chart

As you can see i make chart of this point then add trend line with equation. Now i add intercep at 0, 0 point using "Set intercept" function in chart trend line settings. chart with intercept at 0, 0 point

Of course equation changed. Can anyone tell me how to solve it mathematically?

I have C# application where i calculate approximation but now i need functionality like in Excel - calculate approximation with intercept at point 0, 0.

goliat26
  • 13
  • 4
  • You should look into least squares fitting of polynomials. The only difference between the two is the form of polynomial you assume: one has a constant coefficient and the other does not. – duffymo Jan 13 '21 at 18:26
  • Can you say something more? Should i use least squares to approximation my points or use it on equation which i get from trend line? If you have any example or somethink that help me to understand it,please send it :). – goliat26 Jan 13 '21 at 19:30
  • Let's keep it simple: Your trend line is generated from your data using least squares. y = c0 + c1*x + c2*x^2 + .... You calculate the coefficients c0, c1, c2, .... from your data. If you happen to assume that c0 = 0.0 nothing changes. You're still doing least squares fitting. – duffymo Jan 13 '21 at 19:33
  • Sorry i dont understand. Yes i have calculated coefficients of 2 degree polynomial. But i can't just set c coefficient to 0 without modify another coefficients because it will destroy all formula and data. – goliat26 Jan 13 '21 at 20:43
  • Exactly. They are different when you set the intercept to zero. That's what is happening. It's a new equation. You don't just set c0 = 0; you have to recalculate all the coefficients. – duffymo Jan 13 '21 at 20:45
  • Yes. I know it:). But how? Now i know from John Coleman post but maybe it's possible to find "global" method. – goliat26 Jan 13 '21 at 21:02
  • This isn't a difficult problem - solved many times over. Go learn how to do linear regression. – duffymo Jan 13 '21 at 23:08

1 Answers1

0

For any fixed size, you could use the function =LINEST and the fact that if y = b*x + ax^2 then y is linear in the two (correlated) variables x and x^2:

enter image description here

In A12:C12 I entered the array formula (Ctrl + Shift + Enter` to accept)

=LINEST(A2:A10,B2:C10)

and in A14:B14 I entered

=LINEST(A2:A10,B2:C10,FALSE)

Note that in this approach it was more natural to place the y column before the x column.

Similar approaches work for higher, though fixed, degree. For something more flexible, you might want to port your C# code to VBA.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • Thanks. I will try it. Can i use similar method (with greater powers) to find a,b,c,... of greater degree polynomial? – goliat26 Jan 13 '21 at 20:38
  • @goliat26 In principle, yes (for any fixed degree), though it wouldn't be easy. Cramer's rule itself will cease to be useful. You can work out the equations that need to be solved, load the coefficients into a matrix, and use a `=MMULT(MINVERSE(LHS), RHS)` approach to solving them. This probably isn't all that bad for cubics or even quartics, though I don't see how to implement it for arbitrary degree using just spreadsheet formulas. – John Coleman Jan 13 '21 at 20:47
  • Goal for me is to implement this to C# soo Excel functions are not for me:). For 2nd degree, method from your post is good but i think for future and global method. Now i have implemented approximation based on Gauss method and making derivative. – goliat26 Jan 13 '21 at 20:55
  • @goliat26 I realized that I was being a little bit of an idiot in working through the details of least squares explicitly. I had forgotten that `=LINEST` was able to handle multiple variables. I rewrote my answer completely. – John Coleman Jan 13 '21 at 21:22
  • for me previous version of post was better because it was working for me and was easy to implement in C#. Now, post is based on LINEST Excel funcion soo i cant implement it in C#. Using Excel i only want to understand how its work but in C# i have to calculate this manuały. – goliat26 Jan 13 '21 at 21:36
  • Ok now i have set of point (x, y) => (-0.3, 2), (1, 0.6), (1.9, 3). Based on this article ( https://neutrium.net/mathematics/least-squares-fitting-of-a-polynomial/ ) i make similar matrices, calculate determinants of matrices and calculate coefficients. Everythink is fine and the same as in Excel chart with equation. But what i have to do now with matrices or coefficients to force curve to intercept origin? – goliat26 Jan 14 '21 at 17:35
  • @goliat26 Drop the first row and first column from the system. Since you have one fewer unknowns (coefficients) there is one fewer column, and since there is one fewer equation, you have one fewer row. In the linked to article, the columns and rows are ordered so that you would delete the first one. Everything else is as before. – John Coleman Jan 14 '21 at 18:17
  • Thanks. It works :). I tested for 2nd and 3rd degree of polynomial and it is beautiful. I have only one question. Can you tell me how can i calculate R^2 value? – goliat26 Jan 14 '21 at 18:57
  • When i try calculate R^2 using formula (sum of (calculated yi - average of all real y)^2) / (real yi - average of all real y)^2 i got stupid values like 1.5. Whats wrong with it? On Excel chart i have R^2=0,8988 and all my calculated coefficients are the same as on Excel chart. – goliat26 Jan 14 '21 at 23:28
  • And one more... Is setting intercept with another point on Y axis easy as intercept with point (0, 0) and deleting first row and column from matrix? I know that i write a lot of comments but it is important for me :). – goliat26 Jan 15 '21 at 08:03
  • @goliat26 If `k` is the target y-intercept, just subtract `k` from all of the y-values, then do regression with passing through the origin. This gives you the right coefficients for the non-constant terms in the polynomial. Just add in `k` as the constant term. – John Coleman Jan 15 '21 at 16:43
  • Thanks. It is great :). Now i have to calculate "only" R^2. I tryed a lot of times and a lot of formulas but everytime i got unexpected values. – goliat26 Jan 15 '21 at 17:49
  • Maybe you can ask a different question – John Coleman Jan 15 '21 at 17:49
  • What do you mean? – goliat26 Jan 15 '21 at 18:48
  • @goliat26 You are asking new questions in the comments, which isn't the purpose of comments – John Coleman Jan 15 '21 at 19:37
  • A moment ago i tried to calculate it one more time and i got correct result so everything is ok :). Big thanks for help @John Coleman. – goliat26 Jan 15 '21 at 22:37