Statistical Analysis with Excel Introduction Many professionals use various tools to make their jobs easier. Spreadsheets can greatly improve the accuracy and efficiency of repetitive and common calculations; therefore, engineers often employ spreadsheet applications in their work. In this activity, you will work with other students in probability and statistics to collect data and use a spreadsheet program (such as Microsoft Excel or Google Sheets) to perform statistical analyses and create statistical charts of your data. Procedure Part 1. Perform a statistical analysis in Excel of height measurements of students in your class. 1. Open an Excel workbook. On worksheet 1 type “Statistical Analysis with Excel” in cell A1 and your name in cell A2. 2. Create a survey that can be shared with the class to gather information needed to complete this assignment. You will need to gather survey results from at least 15 other high school students. You will need to know the following information from each student: a. Student Height (in feet and inches) b. Gender (male or female) c. Age Note: You will only be performing statistical analysis of the student height, but you should keep record of the age and gender for comparative analysis at the end. 3. After administering your survey, Input the raw data into an Excel worksheet using a separate column (A) for feet and a separate column (B) for inches. Include appropriate data (column) headers. 4. Using a formula, convert each height to decimal feet and place the results in column C using an appropriate column heading. 5. Format the height measurements in decimal feet to show two decimal places. 6. In the cell just below the column of heights in decimal feet, calculate the sum of the height measurements using the SUM function. Format the cell containing the sum to display a box around the number and add the text “Sum =” in the cell to the left of the sum cell. Note that the sum should display two decimal places. OPTIONAL: Use formulas to calculate the standard deviation of your height data. 7. Calculate the statistics indicated in the image to the right. Create the text labels in the appropriate cells. Be sure to calculate the population standard deviation (STDEV.P) and the sample standard deviation (STDEV.S) as well as the appropriate Mode function (single or multimodal). If your data has more than one mode, use the MODE.MULT function. This function will create an answer in the form of an array. Therefore, before typing the function into a cell, highlight multiple cells (vertically), type in the function and select the range of values. Then depress Cntl/Shift/Enter keys simultaneously to indicate an array will be returned. You can create a simple formula to calculate the range. 8. Use the Data Analysis tool to calculate the Descriptive Statistics (Summary Statistics) and place the output data next to your calculated statistics. 10. Create class intervals (value ranges) for a histogram at 0.25 feet intervals that will include your minimum and maximum recorded height. 11. Create a frequency table using the Histogram tool choice in the Data Analysis Tool (Data tab, Analysis panel). 12. Create a histogram using the 2D chart tool. Format the chart as shown below. 13. Print out your worksheet. Include all of the elements shown on the worksheet below. Conclusion 1. After you complete the survey and data analysis, Mrs. Jackson will assign you a partner to work with to complete this project. You and your partner should discuss the following topics and work together to create a summary to submit summarizing your data analysis using a spreadsheet and the discussions you had with one another about this project. Each group of 2 will only submit one summary for a grade but be sure to include both your and your partner’s data analysis worksheet. This summary can be in the format of your choice – a word document, a video, a powerpoint presentation, a prezi, etc…be creative!) a. Discuss the project and challenges you faced in completing the data analysis. Do you prefer using a spreadsheet, a graphing calculator or Geogebra to perform data analysis? Why? b. Compare your individual data analysis worksheets. Why did the numbers vary between the 2 worksheets? c. Do you think age and/or gender has an impact on student height? Why or why not? d. How can a spreadsheet application make the work of a math student easier? e. Other than the calculation of statistics, how could a math student use a spreadsheet application? f. Research and discuss at least 2 real-world uses of spreadsheets.
Purchase answer to see full attachment