It's not a completely automatic process, but it's still pretty easy.
Enter the data, with X values in one column and Y values in the next.
Select the data, run the Chart Wizard (Insert menu > Chart, or the little
chart icon on the toolbar). In step 1 choose an XY (scatter) type, NOT a
line type.
Right click on the series of points in the chart, and select Trendline. The
default trendline is a linear fit. On the trendline dialog, click on the
Options tab and check the Show Equation and Show R-squared boxes. When the
line and equation appear, you should click on the equation, then use the
toolbar icon to increase the displayed decimals in the formula.
To get the slope, intercept, and correlation coefficient in the worksheet,
use
=Slope(<y values>,<x values>)
=Intercept(<y values>,<x values>)
=Correl(<y values>,<x values>)
where <y values> and <x values> are the ranges used to plot the Y and X
values in the chart.
My daughter is doing statistics in her algebra class, and I showed her this
capability, and also how to enter formulas into Excel. After a week or more
on the statistics unit, she finally understands what the best fit means, and
what residuals are. I guess the book didn't have good diagrams, but nothing
beats an interactive process like this, especially when you are not bogged
down doing all the calculations on a pocket calculator.
- Jon
