I have data on the displacement of an object over time and a cubic trendline fits it very well. I know that the initial displacement is 0 so I can tick the "set intercept" box.
However, I also know that the initial velocity is 0 so I would like the coefficient of x to be 0 as well. However, I have no idea how to configure the regression to happen in this way.
How can I configure it in this manner?
I'm using Office for Mac.
EDIT
To make this clearer, Suppose we have times: [1,2,3,4,5,6] and displacements: [0,2,9,28,69,140,225]
We want to fit a polynomial of 3rd degree to the data which would look like this:But because we know the derivative of the function contains the point (0,0) we wish for the coefficient of x, in this case -3.3067, to be 0. How can we configure excel to fit a trendline in this way.
1 Answer
@RajeshS is right, LINEST is your answer. But you need to help it. Here is my data and solution.
I've inserted columns where I've calculated time-squared and time-cubed, and I'll use these in my model. I selected the shaded range F2:H6, typed this formula, then array-entered it by holding CTRL+SHIFT while pressing ENTER (don't enter the curly braces, CTRL+SHIFT does that for you):
{=LINEST(D2:D8,B2:C8,FALSE,TRUE)}
D2:D8 are the known Y values, B2:C3 are the known X values, FALSE means set the constant to zero, and TRUE means give us the extra rows of statistical feedback. For example, R-squared is 0.99929.
The coefficients of the X values are in reverse order in the first output row of LINEST, and we see the constant is zero, as required.
I used these coefficients to compute the fit in column J, shown in the chart as the orange curve, with the data points as blue markers.
