Читать книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller - Страница 50

You use an important statistical technique called linear regression to determine the relationship between one variable, x, and another variable, y. For more information on linear regression, see Chapter 14.

The basis of the technique is a graph that shows individuals measured on both x and y. The graph represents each individual as a point. Because the points seem to scatter around the graph, the graph is called a scatterplot.

Suppose you're trying to find out how well a test of aptitude for sales predicts salespeople's productivity. You administer the test to a sample of salespersons and you tabulate how much money they make in commissions over a 2-month period. Each person's pair of scores (test score and commissions) locates that person within the scatterplot.

To create a scatterplot, follow these steps:

1 Enter your data into a worksheet.Figure 3-24 shows the entered data.FIGURE 3-24: Your scatterplot data.

2 Select the data that go into the chart.In the background of Figure 3-25, you can see the selected cells — B2 through C21. (Including B1 creates the same chart, but with an incorrect title.) The cells in Column A are just placeholders that organize the data.

3 Choose Insert | Recommended Charts from the main menu and then select the chart type from the list on the left of the screen.I chose the first option, resulting in the chart shown in Figure 3-25.

4 Modify the chart.I clicked the generic chart title and typed a new title. Next, I clicked the Chart Elements tool (labeled with a plus sign) and used the resulting menu to add generic axis titles to the chart. I then typed new titles. Finally, I selected each axis title and typed Ctrl+B to turn the font bold. I did that for the chart title, too. The result is the scatterplot in Figure 3-26.

FIGURE 3-25: The initial scatterplot.

FIGURE 3-26: The almost-finished scatterplot.

For the other graphs, that would just about do it, but this one's special. Right-clicking any of the points in the scatterplot opens the pop-up menu shown in Figure 3-27.

FIGURE 3-27: Right-clicking any point on the scatterplot opens this menu.

Choosing Add Trendline opens the Format Trendline pane. (See Figure 3-28.) I selected the Linear radio button (the default) and clicked the two check boxes at the bottom. (You have to scroll down to see them.) They’re labeled Display Equation on Chart and Display R-Squared Value on Chart.

FIGURE 3-28: The Format Trendline pane.

Clicking the Close button closes the Format Trendline pane. A couple of additional items are now on the scatterplot, as Figure 3-29 shows. A line passes through the points. Excel refers to it as a trendline, but it's more accurately called a regression line. A couple of equations are there, too. (For clarity, I dragged them from their original locations.) What do they mean? What are those numbers all about?

FIGURE 3-29: The scatterplot, with additional information.

Read Chapter 14 to find out.