Statistic Excel Assignments | Online Homework Help
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 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 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 Moving the Histogram Edit Chart Elements |
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 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 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 |
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:
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 D8.
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 D11.
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.
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.
3) Identify the range of sale prices in cell D18.
4) Calculate the size of the bins for 25 bins in cell D19.
5) Construct the histogram using the bins calculated. Follow the directions in step5 in the instructions document. Insert the histogram in cell C23.
Describe the distribution of housing prices of homes in upstate New York.
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.
Use the histogram (or the grouped frequency distribution) to answer the following.
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.
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.
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.
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. Compare the distributions for homes with central air and homes without central air. Find the five-number-summary measures for homes with central air and for homes without central air.
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.
14-15) Obtain the descriptive statistics for sale prices for homes without central air and with central air in the dataset. Provide the values below. Round the monetary values to the nearest cent.
Without Central Air With Central Air
Minimum sale price, in cells D45 and E45
Q1, in cells D46 and E46
Median, in cells D47 and E47
Mean, in cells D48 and E48
Q3, in cells D49 and E49
Maximum, in cells D50 and E50
n, sample size, in cells D51 and E51
16) “Write a brief summary statement comparing the sale prices of homes with and without central air using the medians and the interquartile ranges as a general range of prices one would expect to pay for ‘typical’ homes in this region of upstate New York using the statements provided below.
“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.
Homes with central air generally sell for prices than homes without central air.
This is indicated by comparing the home prices between homes with and without central air.
Calculate the expected difference in median costs in cell E58. Keep the same number of decimal places as the values in step14 and step15.
Given this data, one would estimate that homes with central air can be expected to cost about more than homes without central air.
Identify the variability of prices using Q1 and Q3 for each data set in cells D60, F60, D61, and F61. Keep the same number of decimal places as the values in step14 and step15.
The variability of prices for the typical home without central air ranges from about to
as compared to to for homes with central air. Get Statistic Excel homework help today.