Linear Regression
Excel's "Add Trendline" option automatically provides an equation for the Linear Regression option. The model below shows how you can manually create that equation if needed. Also included is a calculation for correlation.
The formula for the slope (cell F18) is:
=((D13*G13)-(E13*F13))/(D13*H13-E16)
The formula for the Y intercept (cell F19) is:
=(F13-F18*E13)/D13
Although not a part of the linear regression calculations, you might find it useful to know how to manually calculate the correlation of the X and Y data. The formula to calculation correlation (cell F20) is:
=(((D13*G13)-(E13*F13))/SQRT((D13*H13-E16)*(D13*I13-F16)))^2
A chart showing the original points and trendline is below:
The Excel-generated trendline formula appears at the top right side of the chart. The manually calculated trendline in the example starts in cell F26 and is copied through the range F26:F33. That formula is below:
=$F$18*E26+$F$19
John:
Here’s a link to a recent post I made on Excel’s regression tools.
link
Your readers may be interested in the Intercept, Slope, RSQ, SumXMY2 functions as well as LINEST for interpretation of their regression.
This way, they can get their statistical measures without disrupting their data table by adding extra helper columns. The extra columns are not needed if the User uses built-in Excel functions. This can simplify data tables to the actual data.
Kelly