REFERENCE IMAGE
The LINEST function has been useful to me more times than I can count. And yet, I don’t see it used often by others (I think the reason is because it’s an array formula). I’ll explain what an array formula is, but this post is also intended to be my personal quick reference for the LINEST syntax. As often as I use LINEST, I still need to look up it up every time!
| Function | Expression | Equation | Returns | |||
|---|---|---|---|---|---|---|
| Exponential base b | y=abx | =LINEST(LN(y) | x) | LN(a) and LN(b) | ||
| Exponential base e | y=aex | =LINEST(LN(y) | x) | LN(a) and b | ||
| Linear | y=mx+b | =LINEST(y | x) | m and b | ||
| Logarithmic | y=aLn(x)+b | =LINEST(y | LN(x)) | a and b | ||
| Polynomial | y=m*x i +n*x i-1 +…+ b | =LINEST(y | x^ | n | m>) | m n and b |
| Power | y=axb | =LINEST(LN(y) | LN(x)) | LN(a) and b |
| Data Orientation | Polynomial Order Formula | |||||
|---|---|---|---|---|---|---|
| Columns | =LINEST([known_y's] | [known_x's]^ | 2 | 3> | [const] | [stats]) |
| Rows | =LINEST([known_y's] | [known_x's]^ | [const] | [stats]) |
I’m going to use a few baseball numbers for the sake of an example. Specifically, plate appearances (PA) and runs scored (R). I’d like to produce and estimation of the number of runs a player would score given their number of plate appearances. I’d also like to know if this linear equation is generally good at prediction runs or not. Don’t worry if you’re unfamiliar with baseball, we’re really just using them as arbitrary numbers.
Keep in mind, I’m using static data in this example. The true power of the LINEST function lies in using dynamic input data. I’ve watched far too many people produce a graph, select a trendline, display the trendline’s coefficients, then copy/past those values elsewhere for use in another formula. If just one piece of input data changes, the entire process is repeated. Why go to all this extra work when the LINEST function automatically does it for you!?
The first step is to select a 5×4 range of cells so that the array formula can return values for every cell in the array. If you don’t select all 20 cells, the function will only calculate values for the selected portion of the array. Next, type in the equation and instead of pressing enter, hit CTRL+SHIFT+ENTER. This is required to establish it an array formula.
Array Formula with LINEST" width="620" height="408" />
I can now create another formula in the form of mx + b by referring to the numbers the LINEST function created for me! You can see this in action below, with my linearly extrapolated value depicted on the chart as a red X. Notice that the coefficients shown next to the trendline match the values in the top row of the LINEST function. The coefficient of determination (R 2 ) is located in the first column, third row. The other values in the array formula include advanced statistical information you wouldn’t have access to by just using the trendline approach!
While that example covers linear data, polynomials include additional syntax. The polynomial’s order is specified by adding a vector on the [known_x’s] argument. Also, be sure to select the appropriate number of cells for the array formula, corresponding to the number of coefficients needed. Since data can be oriented both vertically, or horizontally, there’s a small provision in the formula for whether your data are in columns or rows. Refer to the Quick Reference above. Notice the exponent on to the [known_x’s] is either a , or a ; depending on the orientation of data.
The basic syntax is LINEST([known_y's],[known_x's],[const],[stats]) . The first two arguments are straightforward, but the next two offer several options.
With that in mind, Excel offers several different ways to calculate trendlines on a graph, as shown below. All those can be handled by LINEST as well! Refer to the quick reference above for how to generate coefficients for different trend/regression types.
In conclusion, I hope you’ve found this reference useful. For more reading on the LINEST function, refer to these links for additional details.
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm
http://office.microsoft.com/en-us/excel-help/linest-HP005209155.aspx
Andrew is currently a mechanical R&D engineer for a medical imaging company. He enjoys good food, motivated people, and road biking. He has still not completely come to terms with the fact he will never play center field for the Kansas City Royals.
Quick ProfileI have thousands of interests, I enjoy collaborating with motivated people, and I strive to never be out-worked!Latest Tweet
[rotatingtweets screen_name='saucypony' include_rts='1' tweet_count='1' no_rotate='1' links_in_new_window='1']
