excel hw
ME 291: Engineering Analysis
Spring 2021
Project #2: Statistical Analysis of Historic Weather Data
Due Friday, April 9 at 5:00 pm
No projects will be accepted after the late deadline.
Agricultural Growth, Research, and Innovation Program (AGRI) of Minnesota
would like to hire you to put together a spreadsheet that analyzes historical weather
data. The data that would be analyzed is going to be obtained from MN
Department of Natural Resources webpage. To test your spreadsheet you may use
the Minneapolis/St. Paul Data (given in the Figure 1)
Figure 1: Historic temperature, precipitation, snowfall and snow depth data sets for
Minneapolis, St. Paul; retracted from
https://www.dnr.state.mn.us/climate/twin_cities/listings.html
https://www.dnr.state.mn.us/climate/twin_cities/listings.html
Below are the details of the tasks your spreadsheet should be able to perform:
– The user should be able to copy the data from the website and paste it to the
proper sheet of the spreadsheet. The types of data that AGRI is interested in
are precipitation, snowfall, and average temperature. Please have a separate
spreadsheet to paste each data. Name the sheets as “historical_precipitation”,
“historical_snowfall”, and “historical_av_temp”.
– You should prepare a separate sheet for each type of data that will provide
the statistical analysis of the collected data: The analysis report should have:
o Year interval
o Number of data points
o Number of missing data points (marked with M) (for each month and
year)
o Mean (for each month and year)
o Median (for each month and year)
o St. deviation (for each month and year)
o First, and Third Quartiles (for each month and year)
o IQR (for each month and year)
o Min expected data (for each month and year)
o Max expected data (for each month and year)
o Outliers (for each month and year)
Please provide a proper name for these sheets.
– The statistical analysis sheet should also provide calculation boxes that the
user can use to calculate
o XXth percentile (for any month or annual average)
o XX% confidence interval (for any month or annual average)
These calculations should refer to related input cells (mean, standard
deviation and the like). There should be proper instructions provided to the
user. User should only change the percentile or confidence interval input and
get the result directly.
General guidelines for all calculations
– Every variable should be presented with proper unit next to it
– The spreadsheet should be color coded (for cells that require user input, for
cells that perform the calculations automatically)
– The spreadsheet should be user friendly! You should assume that you are
putting this tool together for an individual that has little-to-no Excel
knowledge.
– The cells that perform calculations must get their values from proper input
cells. Input data should not be entered manually.
Submit:
• Electronic copy (upload to Project 2 Dropbox on D2L):
o Printscreen of the statistical analysis sheets (you do not need to print
the data sheets).
o A guide that explains how to use the statistical analysis sheets (~1
page).
o Excel file
Make sure your name is included on a separate sheet at the beginning of your
spreadsheet and on the cover page of the paper submission.