Report

Using EXCEL 2010: Descriptive Statistics Department of Operations Weatherhead School of Management 1 Installing Data Analysis To do statistics in Excel, first install the Data Analysis Toolpak add-in as follows: For a Mac, go to the following web site to download and install StatPlus LE: www.analystsoft.com/en/products/statplusmacle/ For a PC, use the following instructions: Click the File button on the left corner. 2 Installing Data Analysis (Cont.) Then you will see the following menu. Click on Options. Click 3 Installing Data Analysis (Cont.) In the Excel Options dialog, click on Add-Ins. Click 4 Installing Data Analysis Cont.) Then you will see the following window. There is a Manage section at the bottom. Click on Go. Click 5 Installing Data Analysis (Cont.) In the Add-Ins menu, select Analysis ToolPak. Then click on OK. Click 6 Installing Data Analysis (Cont.) It will take some time to load the package. When finished, click on the Data tab in the main menu. You should see the Data Analysis module on right. 7 The Frequency Table Open the file SoftDrink.xls to create the frequency table. 8 The Frequency Table (Cont.) Note: In this example, the data is given in TEXT form. The frequency table needs to be created manually. Type the titles “Item” and “Frequency” in the columns you want for the frequency table. 9 The Frequency Table (Cont.) Manually type all values in the “Item” column. Be sure not to miss. Coke Classic Diet Coke Pepsi-Cola Dr. Pepper Sprite 10 The Frequency Table (Cont.) The function COUNTIF is used here to calculate the frequencies. This function needs two values: the range and the criteria. Range is the range of cells with the values you want to count. Criteria describes which values in that range are to be counted. 11 The Frequency Table (Cont.) In cell D2 type “=COUNTIF(” Then select the data range. (A2:A51, in this example.) 12 The Frequency Table (Cont.) Type a “,” For the criteria, simply select the cell in the “Item” column whose whose values you want to count. (C2, for example) Press Enter to perform the function in this cell 13 The Frequency Table (Cont.) Type (or copy) the same function for the rest of the items. You will get the following frequency table. =COUNTIF(A$2:A$51,C3) =COUNTIF(A$2:A$51,C2) =COUNTIF(A$2:A$51,C4) =COUNTIF(A$2:A$51,C6) =COUNTIF(A$2:A$51,C5) 14 The Bar Graph for SoftDrink.xls Based on the frequency table you just created, you can get the bar graph. Note: You CANNOT create the bar graph using the sample data directly. How to create the bar graph for the following data is shown on the next slide. Item Coke Classic Frequency 19 Diet Coke 8 Pepsi-Cola 13 Dr. Pepper 5 Sprite 5 15 The Bar Graph (Cont.) First select the data in the frequency table. (C2:D6, in this example.) 16 The Bar Graph (Cont.) Click “Insert” tab in Menu. Then click “Bar”. Then click “Clustered Bar”. 17 The Bar Graph (Cont.) Then you will see the following bar graph. 18 The Bar Graph (Cont.) The column option in Excel creates a vertical bar graph. Click “Insert” tab in Menu. Then click “Bar”. Then click “Clustered Bar”. 19 The Bar Graph (Cont.) Then you will see the following bar graph. 20 The Pie Chart for SoftDrink.xls Creating a pie chart is similar to the bar graph. Note: You CANNOT create the bar graph using the sample data directly. How to create the pie chart for the following data is shown on the next slide. Item Coke Classic Frequency 19 Diet Coke 8 Pepsi-Cola 13 Dr. Pepper 5 Sprite 5 21 The Pie Chart (Cont.) First select the data in the frequency table. (C2:D6, in this example.) 22 The Pie Chart (Cont.) Click the “Insert” tab in Menu. Then click “Pie”. Then click “Pie”. 23 The Pie Chart (Cont.) Then you will see the following pie chart. 24 The Pie Chart (Cont.) Click the pie chart to active Chart Tools. Click To show the percentages, change the layout, as follows. Click “Chart Tools” tab in Menu. Then click “Quick Layout”. Then click “Layout 1”. 25 The Pie Chart (Cont.) Then you will see the following pie chart. 26 The Histogram Open the file Audit.xls. Click “Data” tab in Menu. Then click “Data Analysis”. 27 The Histogram (Cont.) In the Data Analysis menu, select Histogram. Then click on OK 28 The Histogram (Cont.) You will see the Histogram menu. In the input section, select the range with your data in the Input Range. (A2:A21, in this example.) Then in the output section, select the cell where you want to save the result. [Here, the result is put in the same worksheet.] Select Chart Output. Then click on OK 29 The Histogram (Cont.) You will see the following histogram. 30 The Histogram (Cont.) In the histogram menu, the bin range was left blank. However, you can create your own bin ranges, as follows. Type in the bin range first. In this example, the bin ranges are set to the following (< 10, 10 – 15, 15 – 20, and so on). 31 The Histogram (Cont.) In the input section of the histogram menu, enter the bin range you just created. (B2:B7, in this example.) All other settings are the same as before. 32 The Histogram (Cont.) You will see the following histogram. 33 Arithmetic Mean for Salary.xls AVERAGE(number1, [number2], ...) computes the average (arithmetic mean) of the numbers. Number1 is the first number, cell reference, or range for which you want the average. =AVERAGE(B2:B13) 34 Median for Salary.xls MEDIAN(number1, [number2], ...) computes the median of the numbers. Number1 is the first number, cell reference, or range for which you want the average. =MEDIAN(B2:B13) 35 Percentiles for Salary.xls PERCENTILE.INC(array,k) computes the k-th percentile of values in a range. Array is the array or range of data. K is the percentile value. =PERCENTILE.INC(B2:B13,0.25) =PERCENTILE.INC(B2:B13,0.5) =PERCENTILE.INC(B2:B13,0.75) 36 Mode for Salary.xls MODE(number1,[number2],...]) computes the mode of the numbers. Number1 is the first number, cell reference, or range for which you want the average. =MODE(B2:B13) 37 Variance for Salary.xls VAR.P(number1,[number2],...]) calculates the population variance. VAR.S(number1,[number2],...]) calculates the sample variance. =VAR.P(B2:B13) =VAR.S(B2:B13) 38 Standard Deviation for Salary.xls STDEV.P(number1,[number2],...]) calculates the population standard deviation. STDEV.S(number1,[number2],...]) calculates the sample standard deviation. =STDEV.P(B2:B13) =STDEV.S(B2:B13) 39 Descriptive Statistics Open the file Salary.xls. Click “Data” tab in Menu. Then click “Data Analysis”. 40 Descriptive Statistics (Cont.) In the Data Analysis menu, select Descriptive Statistics. Then click on OK 41 Descriptive Statistics (Cont.) You will see the Descriptive Statistics menu. In the input section, enter the data range in Input Range. (B2:B13 here.) In the output section, enter the cells where you want to save the result. [Here the results are put in the same worksheet.] Select Summary Statistics. Then click on OK 42 Descriptive Statistics (Cont.) You will see the following results. 43 Scatter Diagrams Open file Stereo.xls. 44 Scatter Diagrams (Cont.) First select the data range consisting of two columns. (B3:C12, here.) One column contains values for X and the other column for Y. You can also include the row with the column labels. 45 Scatter Diagrams (Cont.) Click the “Insert” tab in the Menu. Then click “Scatter”. Then click “Scatter with Only Markers”.46 Scatter Diagrams (Cont.) Then you will see the following scatter diagram. 47 Correlation and Covariance COVARIANCE.P(array1,array2) computes the population covariance XY. Array1 is the cell range of values for the X variable. Array2 is the cell range of values for the Y variable. COVARIANCE.S(array1,array2) computes the simple covariance SXY. CORREL(array1,array2) computes the sample correlation = population correlation. Open file Stereo.xls as an example (see the next slide). 48 Correlation and Covariance (Cont.) =COVARIANCE.P(B3:B12,C3:C12) =COVARIANCE.S(B3:B12,C3:C12) =CORREL(B3:B12,C3:C12) 49