Good morning,
I was attempting to use the LINEST function to determine the coefficients in the following equation: n(T) = aT^2 + bT + c. I believe that I should be able to find the values of a, b, and c using the LINEST function, but I could use some assistance to ensure that I'm doing it correctly. In the attached image, you'll find my current approach.
However, I've noticed that not everything is accurate. Here are the exact functions that I used:
1.) Polynomial 2nd order:
a = LINEST(U21:U36, V21:V36^2, 1, TRUE)
b = LINEST(U21:U36, V21:V36, , TRUE)
c = LINEST(U21:U36, V21:V36^0, 1, TRUE)
2.) Linear:
a & b = LINEST(U21:U36, V21:V36)
For the linear regression, I believe that the first result corresponds to "a," and the second result corresponds to "b."
Regarding the polynomial 2nd order, I'm uncertain if the "a" factor is correct, and for "c," I assume that it corresponds to the factor in the top right square. Can someone please confirm if I've got this right?
None of your examples of LINEST is correct. See below for details.
-----
First, it is always bests to attach an Excel file that demonstrates the problem. That is out of respect for those who want to help; do not force them to re-enter your data and formulas. And it might result in a more accurate response; we might not be able to reproduce your results because the posted data is rounded.
"If a picture is worth a 1000 words, an Excel file is worth 1000 pictures". (wink)
But when you post images, always include row numbers as well as column names. I had to infer the row numbers from the cell references in your text.
-----
Second, it is always wise to copy formulas from the Formula Bar and paste them into your posting, instead of retyping them.
I was unable to reproduce your results because you posted LINEST formulas of the form =LINEST(U21:U36, V21:V36...).
But then I looked at "x" and "y" in U20 and V20, and I guessed that the actual formulas are of the form =LINEST(V21:V36, U21:U36....). That is, switching the U and V references. Now my results for "a", "b" and "c" appear to be similar to yours, to wit (see the attached Excel file for details):
-----
Finally, none of your examples of LINEST is correct.
The correct usage is demonstrated in AB21:AD25. With that range selected, enter the formula =LINEST(V21:V36, U21:U36^{1,2}, 1, TRUE)
Note: The formula must be array-entered (press ctrl+shift+Enter instead of just Enter) in versions of Excel before Excel 365 and Excel 2016.
Then the coefficients "a", "b" and "c" are in AB21:AD21. And r^2 is in AB23.
RSQ can also be calculated with the formula =RSQ(V21:V36, W21:W36) in W38. W21:W36 ("est y") is based on formulas of the form (W21) =SERIESSUM(U21, 2, -1, $AB$21:$AD$21)
Aside.... When displaying trendline formulas in charts, it is prudent to increase the number of decimal places by clicking the trendline, then Format Trendline Label. For example, note that the coefficient "a" is more accurately -6.94843E-08, not -7E-08.
-----
PS.... Although I recommend the array-entered LINEST formula above -- it is more efficient -- you can calculate the individual coefficients and r^2 with the following non-array formulas:
a: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 1)
b: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 2)
c: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 1, 3)
r^2: =INDEX(LINEST($V$21:$V$36, $U$21:$U$36^{1,2}, 1, TRUE), 3, 1)