Joseph Schmuller - Statistical Analysis with Excel For Dummies

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 14to find out.

In addition to the application in the preceding section, you use the scatter chart to create something like a line chart. The conventional line chart works when the values on the x- axis are equally spaced, as is the case for the data in Table 3-1.

Suppose that your data look like the values in Figure 3-30. Veterans of earlier Excel versions (Excel 2010 and before) might remember having to figure out that a scatter chart with lines and markers was the best way to visualize these data.

Excel now figures this out for you. Selecting the data and choosing Insert | Recommended Charts presents the Scatter with Straight Lines and Markers as the first option — although Excel labels it simply as Scatter. (See Figure 3-31.) This puts you on the road to the appropriate chart.

FIGURE 3-30:These data suggest a line chart, but the x- values aren’t equally spaced.

FIGURE 3-31:Recommended Charts suggests the appropriate chart for the data.

In Chapters 8, 10, 11, and 19, I show you still another use for the scatter chart. In those chapters, I apply Scatter with Smooth Lines. Stay tuned.

Текст предоставлен ООО «ЛитРес».

Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.