You work for the village of Itsybitsy and have been asked to compute some statistics about the admittedly small population. Ever hopeful that this will soon be a booming metropolis, you decide that a spreadsheet is need3ed for this task.
The town is applying for a grant for increased library facilities and you are being asked to produce some general statistics as well as some information about current library usage.
You have been asked to find the averages of a number of statistics based on salary ranges -- that is you want to know the average number of children, pets and cars for low, middle and high income families. You will read in the ranges since they change each year due to the economy.
Additionally, the people want to know what influences library use. One person believes that it relates to the number of pets or children. Another thinks it is the number of cars. Someone even thinks that it relates to the number of bathrooms in the house. You are not going to try to do this analysis but simply produce the number of books borrowed per child, pet, car, and bathrooms.
The census information for each family will be on a worksheet titled Census Data. All of the following values will be read from the spreadsheet:
The income ranges will be on a worksheet titled Income Ranges. Column A will have the category and columns B and C the low end and high end of the range. Low income will be in row 2, middle income in row 3 and high income in row 4. You may assume that all incomes are within these ranges.
You are to produce 2 result pages, to be formatted nicely and have no error indications on them. If you can not compute a value, you should print "N/A" in the field. You may assume that all of your data is valid.
The averages page is to be named Averages and it is to have the overall average and the average by category (low, middle,high income), rounded to 2 decimal places of each the following:
The library usage page is to be named Library and it is to have the following numbers for each family, rounded to 2 decimal places:
Any intermediate worksheets should have names other than the default Sheetn and should follow the two prescribed output sheets.
Reminder: the input will be on worksheets that are not to have any computational work done on them. You will be given sample data to work with, but your work will be graded with different data. It is therefore critical that you NOT do any work on the input sheet and that you assume any of the data will stay the same. What WILL remain the same is the number of data entries and the location of all information.
The first set of sample input and its expected output can be downloaded from census_sample.xlsx and census_sample.pdf respectively. The second is in itsybitsy.xlsx and itsybitsy.pdf.
The name of the workbook is to be begin with your onyen and is to be uploaded to Sakai under Assign7.
You are also to produce a webpage that is to be uploaded to isis as index.html under the folder Assign7. This web page is to contain the two output tables created from the webpage.xlsx data. There are no specific formatting requirements for this page except that it should be well formatted with reasonable margins. The page should ocontain appropriate titles and the tables should be appropriately captioned. You are also to add a paragraph with some simple observations about the results of the analysis.
Grading Criteria: Grading for this assignment is based on the correctness and readability of the results.