- G I J K Fireplaces H Bathrooms 1 2 1 3 0 2 5 4 1 1 3 1 1 5 B 2 2 0 1 St 1 1 4 1 1 5 4 St 1 1 5 3 0 1 5 3 0 1 5 7 0 1 3 N 1 (60.38 KiB) Viewed 133 times
I J K 4 N 2 2 55 2 3 1 0 2 2 3 А B с D E F G H 1 Price Lot Size Living Area Pct College Central Air Bedrooms Fireplaces Bathrooms 44 150000 0.78 1554 71 1 3 1 1.5 45 234900 0.89 1976 64 1 3 0 2.5 46 279550 1.34 2479 71 0 4 1 2.5 47 246500 1 2714 71 0 4 1 2.5 48 124000 1 797 52 0 2 0 1 49 138000 0.27 1500 71 0 3 0 2.5 50 290000 0.71 1838 71 0 4 0 2 51 108000 0.9 1042 71 0 1 2 52 134900 0.24 1782 52 1 3 0 2.5 53 64500 0.06 1480 52 0 2 1 2.5 54 142000 0.55 1426 52 0 3 1 2 125000 0.34 1404 52 0 3 0 2 56 88000 0.19 1480 52 1 3 1 1.5 57 135000 0.23 1344 52 0 3 0 1 58 90000 0.07 1480 52 1 2 1 2.5 59 90100 0.09 1480 52 3 1 1.5 60 126900 0.25 1596 52 0 3 0 2.5 61 175000 0.47 1768 52 0 3 1 2 62 158000 0.36 1348 52 1 2 1 2 63 92000 0.07 1480 52 0 3 1 1.5 64 82800 0.11 1483 52 0 3 0 1 65 140000 0.23 1576 52 1 4 1 1.5 66 171000 3.16 1008 52 0 2 0 1 67 200640 0.08 2332 52 0 4 0 2.5 68 139000 0.57 1064 52 0 3 1 1 69 225000 0.5 2428 52 1 4 1 2.5 70 182000 1 1310 52 3 1 2 71 208767 0.5 1662 52 1 3 0 2 72 186000 0.55 2000 52 0 4 0 2.5 73 93000 0.1 1480 52 0 3 1 1.5 74 257386 0.5 2969 52 1 4 1 4 75 161000 0.31 1542 52 1 3 0 2.5 76 92000 0.28 1527 52 0 4 0 2 77 211002 0.06 2195 52 0 4 0 2.5 78 115000 0.06 1480 52 0 3 1 1.5 79 113000 0.46 1452 52 1 3 0 2 80 216300 0.1 2360 52 0 4 0 2.5 81 145000 2.12 1440 52 0 3 1 1 82 114000 0.06 1480 52 0 2 1 2.5 83 139050 0.21 1879 52 0 3 0 2.5 101000 Instructions Problem Data 2 B 0 3 2 4 3 3 4 Od 01 2 2 +
Step Instructions Points Possible 1 0 2 8 Start Excel. Download and open the workbook named: Descriptive_Numerical_Analysis_Start. 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 11 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 116 on the Data worksheet, type Q1. In cell J16, calculate the value of the first quartile for the Price data set. In cell 117 on the Data worksheet, type Q3. In cell J17, calculate the value of the third quartile for the Price data set. In cells 07-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. In cell D18, identify the range of sales prices. In cell D19, calculate the size of the bins for 25 bins. 3 1 4 4 1
D7 fo A B 1 1 Problem Housing Data The present study investigates the distribution of sale prices for homes in a region in upstate New York and 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 there. Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. 1-2) Obtain the descriptive statistics for sale prices for all homes in the dataset. Provide the values below: 10 Identify the mean sale price to the nearest cent in cell D7. Identify the standard deviation of the sale prices to the nearest cent in DS. Identify the minimum sale price to the nearest cent in cell D9. Identify the first quartile sale price to the nearest cent in cell D10. Identify the median sale price to the nearest cent in cell Dll. Identify the third quartile sale price to the nearest cent in cell D12. Identify the maximum sale price to the nearest cent in cell D13. Identify the sample size in cell D14. 11 12 13 14 15 To begin to construct the histogram of sale prices, we need to determine our bins. These are the upper limits to the size of the bars on the x-axis. We do this by calculating the range of the prices, and dividing by the number of bars we want in our histogram. Since we have a large sample size, let us choose to have about 25 bins. 16 17 16 3) Identify the range of sale prices in cell D18. 4) Calculate the size of the bins for 25 bins in cell D19. 19 5) Construct the histogram using the bins calculated. Follow the directions in step5 in the instructions document. Insert the histogram in cell C23. 21 22
F26 X fr A G H 22 23 24 Describe the distribution of housing prices of homes in upstate New York. 25 26 27 6) In cells F26-G26, choose how you would describe the shape of the distribution from the drop-down menu. 7) In cells F27-G27, choose which measure of 'center' is best to describe the distribution from the drop-down menu. 8) In cells F28-G28. choose how to compare the values of the mean and the median from the drop-down menu. 9) In cells F29-G29, choose which measure of 'spread' is best to describe the distribution from the drop-down menu. 2B 29 30 31 Use the histogram (or the grouped frequency distribution) to answer the following. 32 33 34 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. 35 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. 36 37 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. 38 39 40 13) Suppose you are looking at two houses in the same neighborhood that are about the same size except one of the homes has central air and the other one does not Using the same bins you used in question 3), construct separate histograms for the sale prices for homes without central air and the sale prices for homes with central air. Title each histogram so that they are labeled appropriately by central air status. Insert the two histograms in cells B42-G42. 41
5 4 6 1 7 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. In cells F26-G26, choose how you would describe the shape of the distribution from the drop- down menu. In cells F27-G27, choose which measure of 'center' is best to describe the distribution from the drop-down menu. In cells F28-G28, choose how to compare the values of the mean and the median from the drop-down menu. In cells F29-G29, choose which measure of 'spread' is best to describe the distribution from the drop-down menu. 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. 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. 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 8 1 9 1 10 1 11 1 12 1
Step Instructions Points Possible 13 8 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. 14 7 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 15 7 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.