need help with this assignment

Even with hand calculators that include statistical functions, determining the standard deviation, or variance, or even the mean for large data sets can be tedious. Excel, however, deals with such tasks readily. Like all business spreadsheets, Excel is laid out so that data can be arranged in rows, columns, or both. Although the commands vary for different kinds of software, all spreadsheets will produce descriptive statistics and most programs, including Excel, will also complete some of the basic statistical tests.

As a point of information, the command structure in Excel changes modestly with the different versions of the software. The commands provided in this book are specific to the version of Excel in Office 2010. Earlier versions of the software may have slightly different commands, but the processes described are always accessible. A brief Web search will usually provide the alternate approach.

There are two ways to get descriptive statistics in Excel. They can be calculated directly by entering the commands for the standard deviation, or for whatever value is needed. The standard deviation can also be produced as part of the "Descriptive Statistics" command. It will be done here both ways, first with the individual commands.

An account manager at an advertising firm sends out a customer satisfaction survey to the 12 clients she developed advertising campaigns for over the last year. The 12 clients register the following scores on the survey the manager sent: 11, 14, 14, 15, 17, 17, 17, 19, 22, 22, 23, 27.

Navigating Excel

The individual boxes in an Excel spreadsheet are called "cells." Each cell is identified by its column and row.

· The columns are labeled from left to right, alphabetically from column A.

· The rows are numbered from 1 down the left side of the window.

· Cell "A1" is the cell in column A and row 1—the upper left. The next cell down is cell A2, and so on.

· When identifying a cell, the column letter is first, and then the row number.

· When cell locations are entered in Excel there is no space between letter and number.

The version of the software for this text is from Office 2010. As Excel has been updated from version to version, the appearance and some of the procedures have changed, but changes in the command structure have been relatively minor.

The steps for entering the customer satisfaction data into the spreadsheet are as follows:

· Place the cursor in the cell where the data are to begin—cell A1, for example, either • by moving the cursor with the arrow keys in the key-pad, • by clicking the mouse on the particular cell, • or by using the touch-pad on a laptop.

· In cell A1, key in the number "11," followed by the "Enter" key. The Enter key will move the cursor to the next cell down.

· Enter each of the other 11 values so that the data are arranged vertically in all the cells from A1 to A12. That will make the spreadsheet look as it does in the video in Figure 1.1.

Figure 1.1: Navigating Excel*

Entering the Command for the Mean

In Excel, when the initial entry in any cell is an equal sign, "=," it indicates that a mathematical operation will follow. The operation can be entered either as one of Excel's programmed commands, such as "average," which is the command for the mean, or as a formula that the user can construct with mathematical operators. To calculate the mean for the 12 customer satisfaction scores and have that value appear in cell A13:

· Place the cursor in cell A13.

· Enter the command, =average(a1:a12) which will calculate the mean for the data in cells A1 to A12. Note the Excel command is "average" rather than "mean."

· Press Enter.

· The value in cell A13 is the mean, 18.16667.

· In the Home tab, click the arrow in the bottom right corner of the Number tab—it's in the middle near the top of the screen.

· Under Category, click Number (the Format tab for Mac users) and then to the right you can indicate the number of decimal places. We'll round to three decimal places. That will make M = 18.167.

Figure 1.2: Entering the Command for the Mean*

Using the Descriptive Statistics Option

Entering the specific command works well when a particular statistic is needed, but sometimes several statistics are needed. The mean is reported with other descriptive values as part of a descriptive statistics option that is available to PC users but, unfortunately, is not present on Mac versions of Excel. For the customer satisfaction data list, the commands for that package of statistics are the following:

· Click the Data tab, which is the sixth tab at the top of the page. (For Mac users, click the Formulas tab, and then fx. Selecting the Statistical option from the list will indicate the various statistical procedures that are available.)

· Click the Data Analysis window at the extreme right just below the tabs. This will open a small window in the page with a list of options (see video in Figure 1.2).

· If the "Data Analysis" option doesn't appear at the extreme right, PC users can "add it in." The commands vary with the different versions of Microsoft Office, but for Excel 2010 the commands for adding in the Data Analysis option are as follows:

1. Click the File Tab in the upper left corner of the screen.

2. Click Options toward the bottom of the left column

3. In the Excel Options window that appears, select Add-Ins in the left column. This will produce a list of Inactive Application Add-Ins, one of which is Analysis ToolPak.

4. Click on Analysis ToolPak.

5. Click Go toward the bottom of the View and manage Microsoft Office Add-Ins window.

6. In the window there will now be a check beside Analysis ToolPak. Click OK.

At this point, clicking the Data Tab, sixth from right at the top of the page, will reveal the Data Analysis option at the extreme right.

· Click the Data Analysis option and then in the Data Analysis window that appears in the middle of the page.

· Click on the Descriptive Statistics option and then click OK.

· In the small window labeled Input Range type in the cells for which we wish the values to be included, A1:A12, just as we did when we entered the formula for the mean. When entering the letter for the column, it doesn't matter whether it's upper- or lowercase.

· Note that the default is that data are Grouped by columns. If the data were listed along a row, we would have to change the default.

· Click Output Range and indicate where the results display is to begin, perhaps cell C1, so that results are next to the original data but not over top of them.

· Finally, click the particular output we wish, which is, Summary Statistics.

· Click OK.

Figure 1.3: Using the Descriptive Statistics Option*

*To see this tutorial without video, please download the PDF version of this chapter.

The output includes more descriptive statistics than have yet been discussed. In addition to the median (Mdn), the mode (M), the lowest and highest values, the range (R), the sample standard deviation (s), and the variance (s2) values, there are also skewness and kurtosis values. Those descriptive statistics will come up in Chapter 2 as part of the discussion of data normality. The standard error will come up later in the book.