I am struggling with Excel right now. What I have is a graph which can be approximated by a polynominal formula. I have values for X and Y Axis and want to get the equation of the trendline. To stay flexible I want a solution which is capable of handling different degrees with "n" possibilites such as y = x0 + z1*x1^2 + z2*x2^2 + ... + zn*xn^n.
Now I assumed that I can Use RGP() (German Excel version of the LINEST() function in English) but I get totally different results compared to the result of a trendline i put into the diagram (excel can show the formulas of trendlines in diagrams).
What I did is:
- Column U: All Y Values (10 values)
- Column V: All X Values (10)
- Column W: All Y^2 Values
- Column X: All X^3 Values
- Column Y: All X^4 Values
Matrix Formula in AA4:AE4 with =RGP(V3:V12;W3:Y12;1;1)
The Results are 4 Different Values. But they have nothing to do with the results I got from the Excel Diagram and the automatically calculated line.
Can someone help me out here? Thanks!
Attached the example list I have:
╔═══════╦═══════╗ ║ Col U ║ Col V ║ ╠═══════╬═══════╣ ║ 10 ║ 272.2 ║ ╠═══════╬═══════╣ ║ 20 ║ 137.8 ║ ╠═══════╬═══════╣ ║ 30 ║ 77 ║ ╠═══════╬═══════╣ ║ 40 ║ 46.7 ║ ╠═══════╬═══════╣ ║ 50 ║ 30.3 ║ ╠═══════╬═══════╣ ║ 60 ║ 20.7 ║ ╠═══════╬═══════╣ ║ 70 ║ 14.8 ║ ╠═══════╬═══════╣ ║ 80 ║ 11 ║ ╠═══════╬═══════╣ ║ 90 ║ 8.5 ║ ╠═══════╬═══════╣ ║ 100 ║ 6.7 ║ ╚═══════╩═══════╝