Notice that the coefficients shown next to the trendline match the values in the top row of the LINEST function. 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.

This is required to establish it an array formula. Next, type in the equation and instead of pressing enter, hit CTRL+SHIFT+ENTER. If you don’t select all 20 cells, the function will only calculate values for the selected portion of the array. 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. Why go to all this extra work when the LINEST function automatically does it for you!? Using LINEST If just one piece of input data changes, the entire process is repeated. 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. The true power of the LINEST function lies in using dynamic input data. Keep in mind, I’m using static data in this example. Don’t worry if you’re unfamiliar with baseball, we’re really just using them as arbitrary numbers. I’d also like to know if this linear equation is generally good at prediction runs or not. I’d like to produce and estimation of the number of runs a player would score given their number of plate appearances.

Specifically, plate appearances (PA) and runs scored (R). I’m going to use a few baseball numbers for the sake of an example.

Excel trendline equation