1 / 27

Statistical Analysis with Excel

Statistical Analysis with Excel. Learning Objectives. Be able to use the Dial Caliper to measure Be able to use Microsoft Excel to Calculate mean, median, mode, standard deviation, maximum and minimum values Create a frequency chart Create a histogram. Dial Caliper Interpretation: Practice.

felicianol
Download Presentation

Statistical Analysis with Excel

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. Statistical Analysis with Excel

  2. Learning Objectives • Be able to use the Dial Caliper to measure • Be able to use Microsoft Excel to • Calculate mean, median, mode, standard deviation, maximum and minimum values • Create a frequency chart • Create a histogram

  3. Dial Caliper Interpretation: Practice • Continue taking measurements. • Each person take 5-7 cubes, measure and record their values, then trade with three other people so you will have 28 measurements. • Your task is to complete the measurements at the beginning of class today. • Later in class we will take a look at using MS Excel to help with data analysis.

  4. Spreadsheet Programs • First developed in 70s • VisiCalc • Dan Bricklin and Bob Frankston • Operated on Apple II • Not patented • Excel based on earlier spreadsheet

  5. Purpose of a Spreadsheet • Store raw data • Make calculations • Analyze data • Create charts to represent data Get out your notes and open Microsoft Excel.

  6. Basics of Excel • Column labels are letters – A, B, C • Row labels are numbers – 1, 2, 3 • A cell is the intersection of a column and row – C12 • Worksheet • all of the cells on a “page” • Workbook • collection of worksheets • Excel file

  7. Basics of Excel • Change worksheet name • Double click on sheet name • Type new name • Enter • Change the name of the worksheet to ‘Heights’

  8. Store Raw Data • Create appropriate heading(s) for data • Input data • Type by hand • Copy from electronic table • Data array • Row • Column • Table Input height data as shown.

  9. Calculations • Cell designations used to indicate values • Order of operations • Parentheses ( ) • Exponents ^ • Multiply * • Divide / • Add + • Subtract – Formula Bar 16 148 48 3 4

  10. Calculations Formula Bar • Always begin with = • Use arrow keys or click in cell to select cells for values in formula • Arrow left to A5 • Type + • Arrow left to B5 • Type / • Type 12 • Enter Input a formula to calculate height in decimal feet for the first height.

  11. Calculations • To copy a formula • Select cell to copy • Right click • Copy • Select the range of cells to copy to • Right click • Paste Create a heading and copy formula to appropriate cells.

  12. Raw Data and Calculated Values • Format cells • Select data range • Right click within range • Select Format Cells • Adjust format • Number • Decimal places • Alignment • Horizontal Change format of height to 2 decimal places.

  13. Calculations Copy the formulas into your notes. • Functions • Defined mathematical calculation

  14. Calculations • Functions • SUM (array) • Gives the sum of an array • Find the sum of the heights in decimal feet. • Select the cell for output • Type = • Type SUM( • Select the range of numbers to sum • Enter

  15. Functions • Use functions to find the following statistics for the set of height measurements • Mean • Mode: • Standard Deviation (population) • Minimum • Median • Maximum Use your notes to find the formulas.

  16. Loading the Analysis Toolpak • File -> Options • Click Add-Ins -> Manage box -> Excel Add-ins • Go • Add-Ins available box, Select the Analysis ToolPak check box ->OK

  17. Frequency Chart and Histogram • Frequency Chart • indicates the frequency of occurrence within a class interval • Histogram • graphical representation of the Frequency Chart

  18. Frequency Chart and Histogram • Select class intervals • Use 0.250 ft intervals for height data • Define Bin values • Bins give the boundary point between class intervals • Values less than or equal to bin value (but greater than previous bin value) are counted 1) Create the Bin chart for the decimal height measures.

  19. Frequency Chart and Histogram • Select the Data Analysis Tool • Data tab > Analysis panel

  20. Frequency Chart and Histogram • Select the Data Analysis Tool • Data tab > Analysis panel • Choose Histogram tool • OK

  21. Frequency Chart and Histogram • Select the Data Analysis Tool • Data tab > Analysis panel • Choose Histogram tool • OK • Select Input Range • Select Bin Range • Choose Output Range • Select a cell on the worksheet • Select ‘Chart Output’ • OK x

  22. Selecting Input Range 1) Click on 2) Click and Drag to Select the Values to Graph 3) Hit ‘Enter’

  23. 1) Click on Selecting Bin Range 2) Click and Drag to Select the Bin Values 3) Hit ‘Enter’

  24. Chart Output 1) Click on ‘Output Range’ Button 2) Click to check ‘Chart Output’ 3) Click on a Cell on the Worksheet to select where the Histogram will show up. 4) Click ‘OK’

  25. Frequency Chart and Histogram Histogram Frequency Chart

  26. Correct Data Labels 1) Double click on ‘Histogram’ and change to ‘Heights of Students’ 3) Double click on ‘Frequency’ and delete it. 2) Double click on ‘Bin’ to ‘Height’

  27. Excel Worksheet Using the data from the Cube Measures Find the Mean, Median, Mode, Standard Deviation and create a histogram of the data. Push: Experiment with the Histogram, display other statistics on the data. YourNameCubeStatistics And complete the 3.3 Worksheet

More Related