Step 1: Create an eight-question survey in a Microsoft® Word document:
Open a new Microsoft Word document, and save it as Survey in your Unit 4 folder.
Create an eight-question survey that you will test with your friends and family. Your survey must ask for certain information (listed below), but you can make up an additional three questions. Your survey should ask the people in your sample for the following information:
height
number of siblings
number of times per month they go to the movies
age
hours of sleep they get each night (on average)
Ask three more questions of your choice—be creative! Just make sure that you are asking questions whose answers are numbers (for example, your question may start with “How much…” or “How many…”).
Make sure that you save your document when you are finished.
Step 2: Create a database where you will enter your data:
Click on the link below to access the Portfolio workbook. Save it as Data Analysis in your Unit 4 folder.
computer mouse icon Portfolio
Select the tab titled Survey Results. Type a one-word heading or field name in each column that represents each question (for example, Age, Height, Weight, etc.).
Step 3: Conduct your survey:
Ask your friends or family members to answer your survey. You can ask your respondents questions in person, over the phone, or via e-mail. Jot down their answers in a notebook or on a piece of scrap paper.
Step 4: Enter the results of you survey into your database, making sure each row represents one person’s answers for every question on the survey.
Step 5: Insert and format a scatter plot displaying the two variables for age and number of hours slept every night.
To insert and format a scatter plot in Microsoft Excel, follow these steps:
1. Open the Data Analysis workbook and navigate to the Survey Results tab where you entered all the survey data.
2. Select the columns for age and number of hours slept each night by clicking on the column headers.
3. Click on the Insert tab in the Excel ribbon at the top of the screen.
4. In the Charts group, click on the Scatter chart type. Choose the scatter plot option that displays points only (without connecting lines).
5. Excel will generate a scatter plot based on the data you selected. You may need to adjust the size of the plot and the axis labels to make it more visually appealing and easier to interpret.
6. Format the scatter plot by right-clicking on different elements (such as data points, axis labels, chart title) and selecting Format [Element]. You can change the colors, fonts, sizes, and other aesthetics of the scatter plot to make it more visually appealing.
7. Add a trendline to the scatter plot to show the relationship between age and number of hours slept each night. To do this, click on the scatter plot, then right-click on a data point and choose Add Trendline. Select the type of trendline that best fits the data (linear, exponential, etc.) and display the equation and R-squared value on the plot if desired.
8. Once you are satisfied with the formatting and appearance of the scatter plot, save your Excel workbook and include it in your Unit 4 folder for submission.
By following these steps, you should be able to insert and format a scatter plot in Microsoft Excel to visually represent the relationship between age and number of hours slept each night based on the survey data you collected from your friends and family.