1 / 49

Using EXCEL 2010: Descriptive Statistics

Using EXCEL 2010: Descriptive Statistics. Department of Operations Weatherhead School of Management. Installing Data Analysis. To do statistics in Excel, first install the Data Analysis Toolpak add-in as follows:

theta
Download Presentation

Using EXCEL 2010: Descriptive Statistics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using EXCEL 2010: Descriptive Statistics Department of Operations Weatherhead School of Management

  2. Installing Data Analysis • To do statistics in Excel, first install the Data Analysis Toolpakadd-in as follows: • For a Mac, go to the following web site to download and install StatPlusLE: www.analystsoft.com/en/products/statplusmacle/ • For a PC, use the following instructions: • Click the File button on the left corner.

  3. Installing Data Analysis (Cont.) • Then you will see the following menu. Click on Options. Click

  4. Installing Data Analysis (Cont.) • In the Excel Options dialog, click on Add-Ins. Click

  5. Installing Data Analysis Cont.) • Then you will see the following window. • There is a Manage section at the bottom. Click on Go. Click

  6. Installing Data Analysis (Cont.) • In the Add-Ins menu, select Analysis ToolPak. • Then click on OK. Click

  7. 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.

  8. The Frequency Table • Open the file SoftDrink.xls to create the frequency table.

  9. 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.

  10. 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

  11. The Frequency Table (Cont.) • The function COUNTIF is used here to calculate the frequencies. • This function needs two values: the range and the criteria. • Rangeis the range of cells with the values you want to count. • Criteriadescribes which values in that range are to be counted.

  12. The Frequency Table (Cont.) • In cell D2 type “=COUNTIF(” • Then select the data range. (A2:A51, in this example.)

  13. 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

  14. 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)

  15. 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.

  16. The Bar Graph (Cont.) • First select the data in the frequency table. (C2:D6, in this example.)

  17. The Bar Graph (Cont.) Click “Insert” tab in Menu. Then click “Bar”. Then click “Clustered Bar”.

  18. The Bar Graph (Cont.) • Then you will see the following bar graph.

  19. 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”.

  20. The Bar Graph (Cont.) • Then you will see the following bar graph.

  21. 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 chartfor the following data is shown on the next slide.

  22. The Pie Chart (Cont.) • First select the data in the frequency table. (C2:D6, in this example.)

  23. The Pie Chart (Cont.) Click the “Insert” tab in Menu. Then click “Pie”. Then click “Pie”.

  24. The Pie Chart (Cont.) • Then you will see the following pie chart.

  25. 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”.

  26. The Pie Chart (Cont.) • Then you will see the following pie chart.

  27. The Histogram • Open the file Audit.xls. Click “Data” tab in Menu. Then click “Data Analysis”.

  28. The Histogram (Cont.) • In the Data Analysis menu, select Histogram. • Then click on OK

  29. 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

  30. The Histogram (Cont.) • You will see the following histogram.

  31. 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).

  32. 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.

  33. The Histogram (Cont.) • You will see the following histogram.

  34. 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)

  35. 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)

  36. 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)

  37. 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)

  38. 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)

  39. 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)

  40. Descriptive Statistics • Open the file Salary.xls. Click “Data” tab in Menu. Then click “Data Analysis”.

  41. Descriptive Statistics (Cont.) • In the Data Analysis menu, select Descriptive Statistics. • Then click on OK

  42. 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

  43. Descriptive Statistics (Cont.) • You will see the following results.

  44. Scatter Diagrams • Open file Stereo.xls.

  45. 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.

  46. Scatter Diagrams (Cont.) Click the “Insert” tab in the Menu. Then click “Scatter”. Then click “Scatter with Only Markers”.

  47. Scatter Diagrams (Cont.) • Then you will see the following scatter diagram.

  48. 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).

  49. Correlation and Covariance (Cont.) =COVARIANCE.P(B3:B12,C3:C12) =COVARIANCE.S(B3:B12,C3:C12) =CORREL(B3:B12,C3:C12)

More Related