1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Fuel Efficiency (mpg) 18 20 15 22 17 16 28 20 19 14 25 26 21 30 25 21 13 20 17 23 31 23 17 31 17 Type Truck Truck SUV SUV Truck SUV Car Truck SUV Truck Car Car Car Car Car Car Truck SUV Truck SUV Car Car Truck Car Truck Name:________________________________________________ Spreadsheet: Formulas for each of the following: Mean, Median, Mode, Minimum, Maximum, Range, 25th percentile, 90th percentile, and standard deviation. Fuel Efficiency Frequency Table Fuel Efficiency Histogram Vehicle Type Frequency Table Vehicle Type Graph Report: At least 1 page long Paragraph form (not bullets) Spelling and Grammar Comparison of mean and median. Skew? Calculation and interpretation of student's z-score Comparison of class data to EPA average Description of vehicle types Explain process for making tables and graphs Advantages of tables and graphs Reflect on experience TOTAL: Points Possible 3 pts each (27 total) 4 4 4 4 3 3 3 4 4 4 4 4 4 4 80 Points Earned Central IN - Fall 2016 PROJECT 2: Statistics Project Student Guide The numbered sections in this guide correspond to the numbered steps on your project description. 1. Create your Excel Spreadsheet. a. Open a new Excel spreadsheet. b. Enter your name, course, and project name. c. Enter the fuel efficiency data. You will find the values on the last page of your project description. Be sure to include the fuel efficiency (mpg) and the vehicle type for each data value. 2. Calculate the statistics for the fuel efficiency values. Somewhere on your spreadsheet, set up a table where you will calculate your statistics. It should look something like this: Fuel Efficiency Mean Median Mode Maximum Minimum Range 25th percentile 90th percentile Standard Deviation You will use the Excel formulas to calculate the values to fill in the table. The formulas you need are listed below. Statistic Mean Median Mode Minimum Maximum Range 25th Percentile 90TH Percentile Formula for Commute times =AVERAGE(A2:A26) NOTE: Your cell references will be different. Use the cell locations from your spreadsheet. =MEDIAN(A2:A26) =MODE(A2:A26) =MIN(A2:A26) =MAX(A2:A26) There is no Function to calculate range. Enter a formula that subtracts: Maximum - Minimum. It will look something like this (change the cell references to match your spreadsheet): =F9 - F8 =PERCENTILE(A2:A26, 0.25) =PERCENTILE(A2:A26, 0.9) Central IN - Fall 2016 Standard Deviation =STDEV(A2:A26) 3. Create visual representations of the data. a. Create a frequency distribution table for the data. Somewhere on your Excel spreadsheet, you should set up a table similar to this: Frequency Table Fuel Efficiency (mpg) Frequency 13-16 17-20 21-24 25-28 29-32 b. Calculate the frequencies by hand and type the results in your Excel table. i. For example, you will count how many values in the fuel efficiency column are in the 13-16 range? Fill this number in the first cell on your table. Continue counting for each of the remaining fuel efficiencies until your table is complete. c. Construct a histogram for the fuel efficiency data from your frequency table. See p. 114-115 in your workbook for instructions on how to create a histogram in Excel. d. Create a frequency distribution table for the type of vehicle. Somewhere on your Excel spreadsheet, you should set up a table to look something like this: Frequency Table Vehicle Type Frequency Truck SUV Car e. You will then calculate the frequencies by hand and type the results in your Excel table. f. Create a graph for the vehicle type. See p. 55-56 in your workbook for instructions on how to create a graph in Excel. 4. Calculate the z-score for the fuel efficiency of your own car (or the car of someone you know). This can be done by hand and does not need to be included in your Excel spreadsheet. The results will be included in your written report. 5. Write your report. Be sure to address all of the items listed in the project description. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Fuel Efficiency (mpg) 18 20 15 22 17 16 28 20 19 14 25 26 21 30 25 21 13 20 17 23 31 23 17 31 17 Type Truck Truck SUV SUV Truck SUV Car Truck SUV Truck Car Car Car Car Car Car Truck SUV Truck SUV Car Car Truck Car Truck Name: Course: Project Name: 1 2 3 4 5 6 7 8 9 10 11 Fuel Efficiency (mpg) 18 20 15 22 17 16 28 20 19 14 25 Type Truck Truck SUV SUV Truck SUV Car Truck SUV Truck Car 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 21 30 25 21 13 20 17 23 31 23 17 31 17 Car Car Car Car Car Truck SUV Truck SUV Car Car Truck Car Truck Fuel Efficiency Mean 21.16 Median 20 Mode 17 Maximum 31 Minimum 13 Range 18 25th Percentile 17 90th percentile 29.2 Standard deviation 5.2016023172 bins 16 20 24 28 32 vehicle type barchart 12 10 8 Frequency Table Vehicle Type Frequency Truck 9 SUV 6 Car 10 Frequency 6 4 2 0 Truck SUV Car Vehicle type Fuel Efficiency (mpg) Histogram Frequency Table Fuel Efficiency (mpg) 13-16 17-20 21-24 25-28 29-32 Frequency 4 9 5 4 3 10 9 8 7 6 5 Frequency 4 3 2 1 0 F 13-16 17-20 21-24 25-28 Fuel efficieny (mpg) 29-32 e barchart Frequency V bins Car ype More g) Histogram Frequency 25-28 mpg) 29-32 Frequency 16 4 20 9 24 5 28 4 3 1 Name Course Project Name: Statistics Project Statistics Project Report Exploratory data analysis (EDA) is a composition of statistical techniques that helps in understanding any given set of data. These statistical techniques are grouped into two: descriptive statistics and visual displays. EDA focuses on investigating the distribution assumptions as well as patterns and relationships within and between variables. Descriptive statistics includes the measure of central tendency and measures of spread. As a matter of fact the paper aims to explore the fuel efficiency (mpg) of different vehicle type using descriptive statistics and graphs. Comparison mean and median The mean and median values are very important in describing the normality of a given variable. For the fuel efficiency of three vehicle types (Truck, Car, and SUV) the average is 21.16 mpg and the median is 20 mpg. The mean fuel efficiency is slightly larger than the median fuel efficiency. The comparison of the two statistics indicates a positive skew. In a nutshell, the fuel efficiency data is not symmetrically distributed or simply does not follow a normal distribution. Calculation of z-score The data provided is from a sample of twenty-five vehicles of three different types. Since the population mean and standard deviation are not known the z-score are therefore 2 approximated using the sample statistics. The formula below is used to calculate the z-score using the sample statistics. z= xx s A friend vehicle has fuel efficiency of 23 mpg. The z-score corresponding to this value is given as: z= 2321. 16 =0.3537 5.202 Comparison of class data to EPA average The Environmental Protection Agency (EPA) sets the fuel economy standards at an average of 24 mpg. The aim is to give consumer an estimate that they can use to compare various vehicle models. By direct observation the average of the class mpg data is less compared to the EPA average. On the other hand, it is clear that the EPA average falls within 3 standard deviations of the class data average. It is then appropriate to conclude that there is no significant difference between the class average and the EPA average of fuel efficiency. Vehicle types The class data comprises of three vehicle types the SUV, Truck, and Car. There are nine trucks, six SUVs, and ten cars. Trucks are more built to carry cargo and they vary in power, size and configuration they can include Lorries and pick-ups. SUVs are usually 4-wheel drive vehicles designed for rough or off-road terrain and mostly for passengers' transportation. Cars 3 are smaller in size compared to the SUVs and trucks; carry small number of people; and purposely design to run on roads. Process of making tables and graphs Tables and graphs for displaying data are usually determined by the variables types and the purpose. Frequency tables are made by constructing two columns. One column contains the classes and the other contains the frequency of each class. The number of row are determine by the number of classes or categories depending on whether the data is numerical or categorical respectively as in the case of fuel efficiency and vehicle type. Graphs on constructed by defining the x-axis as the class or category axis and the y-axis to give the frequency of the data for each class or category. Advantage of tables and graphs One important advantage of using tables and graphs is that they give good visual impression of the data. In addition, graphs and tables are easier to interpret compared to observing the entire data set. The patterns, relationships and comparison can easily be identified by use of graphs and tables. Reflection of the experience The current exercise helped in demonstrating statistical skills in exploratory data analysis using both descriptive statistics and graphs. The lack of symmetry in the histogram became clearer after comparing the mean and median values for class data sets. In future, when purchasing of vehicle any given type the current experience will help making sound fuel efficiency comparisons. 4