Hands-on Group Excel Project
April 16, 2014
Camesha Vera, Nadia Ferrari, Shuo Sun, Jiashun Hu, Siddhant Dutt, YuXin Xing
The US Census Service is need of analysis for some recently released data, pertaining to housing in the Boston Massachusetts area. The reports generated from this analysis will be completed using an Excel spreadsheet. Excel features calculations, graphing tools, pivot tables, “what if “ scenarios, along with other data analysis functions. It has been a very widely applied spreadsheet for these platforms, and is considered the industry standard for spreadsheets. The US Census is looking to gather the results for the following scenarios. The function used in the Excel tool is known as a pivot table. In data analysis, a pivot table is a data summarization tool found in data visualization programs or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. In addition, we will use other functions of excel, such as charts, filters & statistics, all calculated from the given dataset. The dataset contains information collected from the StatLib Archive (http://lib.stat.cmu.edu/datasets/boston). The dataset contains 506 cases. The data was originally published by Harrison, D. & Rubinfeld, D.L ‘Hedonic prices & the demand for clean air”, J. Environ. Economics & Management, vol, 81-10, 1978 There are 14 attributes in each case of the dataset. They are:
CRIM per capita crime rate by town
ZN proportion of residential land zoned for lots over 25,000 sq.ft.
INDUS proportion of non-retail business acres per town.
CHAS Charles River dummy variable (1 if tract bounds river; 0 otherwise)
NOX nitric oxides concentration (parts per 10 million)
AGE proportion of owner-occupied units built prior to 1940 RM average number of rooms per dwelling
DIS weighted distances to five Boston employment centers
RAD index of accessibility to radial highways
TAX full-value property-tax rate per $10,000
PTRATIO pupil-teacher ratio by town
B 1000(Bk - 0.63)^2 where Bk is the proportion of blacks by town
LSTAT % lower status of the population
MEDV Median value of owner-occupied homes in $1000
TASK 1-The census department would like to display the neighborhoods that are bordering the Charles River with 6-7 bedrooms, that have an average house value of 25.92 ($000). Using multiple criteria, # of rooms & location.
Average of MEDV
average value of MEDV
TASK 2-The Boston police department is deciding what neighborhoods to allocate funds for public safety and decreasing crime. They would also like to know what effect employment centers have on the crime rate of a neighborhood, and if more should be built.
Average of MEDV
Average of DIS
Data Mining for Business Intelligence, 2nd Edition, Shmueli, Patel & Bruce
Please join StudyMode to read the full document