Descriptive statistics excel 2016

Office 2016 – myitlab:grader – Instructions Excel Project

Descriptive Numerical Analysis V3

Project Description: The present study investigates the distribution of sale prices for homes in a region in upstate New York. This project will also seek to compare the distributions of sale prices between homes with central air and homes without central air. Suppose you are being transferred to this area and are interested in getting a good description of selling prices for homes in this region. The variables collected in the data are: Price: Recorded in dollars; Lot size: measured in acres; Living Area: measured in square feet; Pct College: percent of students going to college; Central Air: No – 0, Yes – 1; Bedrooms: Number of bedrooms; Fireplace: Number of fireplaces; Bathrooms: Number of bathrooms. For the purpose of this project, you will only be using the variables Price and Central Air.

Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download and open the workbook named: Descriptive_Numerical_Analysis_Start. 0 2 Obtain the descriptive statistics for sale prices for all homes in the dataset. Adding Descriptive Statistics Go to the Data worksheet. Select Data Analysis in the Analysis group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Select Descriptive Statistics and click OK. Select the Price column as the Input Range. Choose Grouped by Columns. Check the ‘Labels in the first row’ box. Choose cell I1 on the Data worksheet as the output range. Check the ‘Summary statistics’ box. Do not check any additional boxes in the Descriptive Statistics menu. In cell I16 on the Data worksheet, type Q1. In cell J16, calculate the value of the first quartile for the Price data set. In cell I17 on the Data worksheet, type Q3. In cell J17, calculate the value of the third quartile for the Price data set. In cells D7-D14 on the Problem worksheet, identify the values for the mean, standard deviation, minimum, first quartile, median, third quartile, maximum, and sample size. Round the monetary values to the nearest cent. 8 3 In cell D18, identify the range of sales prices. 1 4 In cell D19, calculate the size of the bins for 25 bins. 1 5 To construct the histogram, first create a column of ‘bins’ on the Data worksheet. Since the bin size is not a round number in thousands of dollars, choose the bin size to be 30,000. In cell K1 on the Data worksheet, type Binsize. In cell K2, type 30000, in cell K3, type 60000, and then drag the sequence down until you pass the most expensive selling price. Now construct the histogram. Inserting the Histogram On the Data worksheet, select Data Analysis in the Analysis group of the Data tab of the Ribbon. Select Histogram and click OK. Select the Price column as the Input Range and the Binsize column (starting on cell K1) as the Bin Range. Check the ‘Labels’ box. Choose cell M1 on the Data worksheet as the output range. Check the ‘Chart Output’ box. Do not check any additional boxes in the Descriptive Statistics menu. Moving the Histogram Select the Histogram. Then choose Move Chart in the Design tab on the Ribbon. Select the Problem worksheet in the provided drop-down menu. Drag the histogram on the Problem worksheet and change its size so that it is located in cell C23. Edit Chart Elements Change the title of the histogram to Distribution of selling prices. Right click on one of the bars and select ‘Format Data Series’. Slide the ‘Gap Width’ to zero. 4 6 In cells F26-G26, choose how you would describe the shape of the distribution from the drop-down menu. 1 7 In cells F27-G27, choose which measure of 'center' is best to describe the distribution from the drop-down menu. 1 8 In cells F28-G28, choose how to compare the values of the mean and the median from the drop-down menu. 1 9 In cells F29-G29, choose which measure of 'spread' is best to describe the distribution from the drop-down menu. 1 10 In cell D34, calculate the percentage of homes rounded to one decimal place whose prices are expected to exceed $300,000 using the value(s) found in step 5. Note: Using the Histogram tool, a value is included in a particular bin if the number is greater than the lowest bound and equal to or less than the greatest bound for the data bin. 1 11 In cell D36, calculate the percentage of homes rounded to one decimal place that are priced between $90,000 and $300,001, exclusive, using the value(s) found in step 5. Note: Using the Histogram tool, a value is included in a particular bin if the number is greater than the lowest bound and equal to or less than the greatest bound for the data bin. 1 12 In cell D38, calculate the percentage of homes rounded to one decimal place expected to be priced less than or equal to $150,000 using the value(s) found in step 5. Note: Using the Histogram tool, a value is included in a particular bin if the number is greater than the lowest bound and equal to or less than the greatest bound for the data bin. 1 13 Construct two histograms showing the distribution of prices for homes without central air and the prices for homes with central air. First, create the two columns of data. On the Data worksheet, copy the Price column to columns P and Q. In cell P1, type Price without central air. In cell Q1, type Price with central air. Then select the first row, go to the Home tab of the Ribbon, and select Sort&Filter in the Editing group. Select Filter in the drop-down menu. In cell E1, open the drop-down menu and select to show only value 0. Delete all of the filtered values in column Q. Then in cell E1 in the drop-down menu, select to show only value 1. Delete all of the filtered values in column P. Go back to cell E1 and select to show all values. Construct the histogram using Data Analysis for the Price without central air column using the Binsize column K (starting on cell K1). Choose cell R1 on the Data worksheet as the output range. Be sure to check the ‘Labels’ and ‘Chart Output’ boxes in the Histogram menu. Move the histogram to the Problem worksheet and place it in cells B42-C42. Change the title of the histogram to Distribution of selling prices without central air. Right click on one of the bars and select ‘Format Data Series’. Slide the ‘Gap Width’ to zero. Construct the histogram using Data Analysis for the Price with central air column using the Binsize column K (starting on cell K1). Choose cell T1 on the Data worksheet as the output range. Be sure to check the ‘Labels’ and ‘Chart Output’ boxes in the Histogram menu. Move the histogram to the Problem worksheet and place it in cells D42-G42. Change the title of the histogram to Distribution of selling prices with central air. Right click on one of the bars and select ‘Format Data Series’. Slide the ‘Gap Width’ to zero. 8 14 Obtain the descriptive statistics for sale prices for homes without central air. Go to the Data worksheet and use Descriptive Statistics in Data Analysis for the Price without central air column. Choose cell V1 on the Data worksheet as the output range. Be sure to check the ‘Labels in the first row’ and ‘Summary statistics’ boxes. In cell V16 on the Data worksheet, type Q1. In cell W16, calculate the value of the first quartile for the Price without central air data set. In cell V17 on the Data worksheet, type Q3. In cell W17, calculate the value of the third quartile for the Price without central air data set. In cells D45-D51 on the Problem worksheet, identify the values for the minimum, first quartile, median, mean, third quartile, maximum, and sample size. Round the monetary values to the nearest cent. 7 15 Obtain the descriptive statistics for sale prices for homes with central air. Go to the Data worksheet and use Descriptive Statistics in Data Analysis for the Price with central air column. Choose cell X1 on the Data worksheet as the output range. Be sure to check the ‘Labels in the first row’ and ‘Summary statistics’ boxes. In cell X16 on the Data worksheet, type Q1. In cell Y16, calculate the value of the first quartile for the Price with central air data set. In cell X17 on the Data worksheet, type Q3. In cell Y17, calculate the value of the third quartile for the Price with central air data set. In cells E45-E51 on the Problem worksheet, identify the values for the minimum, first quartile, median, mean, third quartile, maximum, and sample size. Round the monetary values to the nearest cent. 7 16 In cells D55 and D56, compare the estimated sale prices of homes with central air to homes without central air. Choose the correct answers from the drop-down menus. In cell E58, calculate the expected difference in median costs. In cells D60, F60, D61, and F61, identify the variability of prices using Q1 and Q3 for each data set. Use the descriptive statistics in cells D45-E51. Keep the same number of decimal places as the values in step14 and step15. 7 17 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points 50

Updated: 05/20/2018 1 Current_Instruction.docx