Utilize a dataset posted to the course blackboard site along with this assignment (under ‘practice assignments', then ‘PA: Basic Excel Modeling'). The dataset is titled "water_tower_data.xlsx" and supplies the data recorded from the water tower project demonstration day during three previous EGR 101 class sections. Specifically, in the "raw data" sheet, the file gives the following attributes for 28 teams: team number, water tower design type (speed vs. height), time to build tower (sec), tower height (in), number of cups, and number of rows (levels) in the tower. For this assignment, complete the following tasks and then upload your completed
1. Save the "water_tower_data_2013.xlsx" file to your local machine with the following name: PA_YourLastName (e.g., "Bekki.xlsx" or "Smith.xlsx").
2. Copy and columns A and B and E and F from the "raw_data" sheet into the same locations in the "converted_data" sheet. At the end of this operation, columns A, B, E, and F in the "converted_data" sheet should be identical to their counterparts in the "raw_data" sheet.
3. In the "converted_data" sheet, in cell C1, type "time to complete (min)"
4. In the "converted_data" sheet, in cell D1, type "tower height (cm)"
5. Input a formula in cell C2 of the "converted_data" sheet that convert the "time to complete" value in cell C2 of the "raw_data" sheet (and is in seconds) to a corresponding value in minutes. Drag this formula down to cell C29 (on the "converted_data" sheet) so that the same calculation is done for all 28 teams.
6. Input a formula in cell D2 of the "converted_data" sheet that converts the "tower height" value from cell D2 of the "raw_data" sheet (which is in inches) to a corresponding value in centimeters. Drag this formula down to cell D29 (on the "converted_data" sheet) so that the same calculation is done for all 28 teams.
7. In cell G1 on the "converted_data" sheet, type "score".
8. Add a formula in the "score" column that calculates the score for each team based on the following formula, where tower height is given in centimeters, and time to build is given in minutes. (NOTE: this is not the exact formula that will be used on our demonstration day):
S=Nc +5(NL)+(H/t)
where: S =Score
Nc = Number of Cups
NL= Number of Levels
H = Tower 1 leight in centimeters
t = time to build the tower in minutes
Verbally: score = number of cups + 5(numbcr of levels) 4- (tower height / time to build)
9. Insert the text below in the cells on the sheet titled "averages"