Hello,
I am currently working on a project for which I need to plot a trend line of the form y=ax². I tried the polynomial trend line with the degree set to 2, but it produced a trend line of the form y=ax²+bx+c, where b and c are both not zero. None of the other options seem to be able to do this. How can I accomplish what I need? I strongly prefer to not use addons, but I will if absolutely necessary.
Thank you
I would calculate the needed coefficient manually and calculate the coordinates for the trend line in the spreadsheet.
Assume x-values in A2 to A10 and y-values in B2 to B10. You get the coefficient a by formula
=LINEST(B2:B10;(A2:A10)^2;0).
The result of the formula is a range of two cells e.g. D1:E1, the left one is a, the right one is 0. Then you can generate a third column of values in C starting with =$D$1*A2^2 and copy it down by double-click on the drag-square. Add these new column as additional line into the chart. Keep in mind, that LINEST is an array function and you need to finish your formula with Ctrl+Shift+Enter.
To get additional statistics use =LINEST(B2:B10;(A2:A10)^2;0;1) .
You need not use exact the arguments A2:A10 for your trend line, but you can use an additional, larger argument column with extended values for extra- and interpolation.
You know how to add a line to the chart, which has different source range?
Addition: If you want a nice curved trend line, then style the own calculated points without line and tiny dots and enable a polynomial trend line of grad 2 for it. Because your own calculated points belong to y=ax² curve, the trend line will exactly go through these points.