Engineering Problem Solving I

Engineering Problem Solving I
Excel Analysis Problem

In an effort to better understand the response of a building to cyclic movements, during an earthquake for example, a small scale replica has been built and tested. During the testing a sensor was placed in 6 different locations, near the base, in the middle, near the top, along with two diagonal locations and a vertical location, as illustrated in Figure 1 thru Figure 6. The data from this test is provided in the file “EXCEL Data Analysis Assignment 1.XLSX”. And you are asked to generate characteristic plots, and simple statistical analysis to help determine the relationships between the forces and accelerations at the different locations.

Figure 1. Photograph of the Bottom Force/Acceleration Sensor Location

Figure 2. Photograph of the Middle Force/Acceleration Sensor Location

Figure 3. Photograph of the Vertical Force/Acceleration Sensor Location

Figure 4. Photograph of the Top Force/Acceleration Sensor Location

Figure 5. Photograph of the Top Cross Force/Acceleration Sensor Location

Figure 6. Photograph of the Bottom Cross Force/Acceleration Sensor Location

You need to create a sheet entitled “Summary” that has at least 10total plots, one for Force, X-Direction Acceleration, Y-Direction Acceleration, Z-Direction Acceleration, Resultant Acceleration) for groups of three sensor locations. The first group of sensor locations is Bottom, Middle, and Top leaving the second group as Cross-Bot, Cross-Top and Vertical, as illustrated in Figure 7 and Figure 8, respectively. In addition, build an organized table that shows the average, standard deviation, minimum and maximum values of each of the data types (force and accelerations) for each of the six sensor locations as outlined in Table 1.

Figure 7. Representation of the Bottom, Middle and Top Sensor Location Force Values

Figure 8. Representation of the Cross-Bot, Cross-Top, and Vertical Sensor Location Force Values

Table 1. Sample Configuration of a Data Table for One of the Sensor Locations

A step by step listing of tasks that is to be completed is provided for your success on this assignment.

1. Open the data file “EXCEL Data Analysis Assignment 1.XLSX”
2. Save the file as “EXCEL Data Analysis Assignment ENGR101_#$%_Lastname_Firstname.xlsx” where #$% is the three character section number of your ENGR 101 course.
3. Create a new sheet in the file and name it “Summary”
4. In the Summary Sheet Create
   1. Type in the name of the first data sheet in Cell A1
   2. Table 1 starting in Cell A2
   3. Merge the first row of the sheet such that it is the width of the entire table
   4. Fill in the values in the table with the corresponding functions and columns of data from the names sheet
   5. In Cell A10 type in the name “Force Comparison”
   6. Create a table with all 6 sensor locations indicated as rows and columns as shown in Table 1 above
   7. Fill in the respective data for forces from each of the respective data sheets
   8. Starting in Cell A20 repeat steps f and g for the resultant accelerations from the different sensor locations
   9. In Cell A30 Answer the following question: In looking at the data that you have just summarized, is there any relationship between the sensor locations in regards to the forces and/or resultant acceleration? If so, please describe that potential relationship as well.
5. Create a sheet called “Plots” and place the indicated 10 plots of the respective data in an orderly manner with a Chart Title on each plot to indicate which it is. *Note all other required elements of the chart should be present as well*

• Forces – Low, Middle, High
• Forces – Cross-Bot, Cross-Top, Vertical
• X-Dir Acc. – Low, Middle, High
• X-Dir Acc. – Cross-Bot, Cross-Top, Vertical
• Y-Dir Acc. – Low, Middle, High
• Y-Dir Acc. – Cross-Bot, Cross-Top, Vertical
• Z-Dir Acc. – Low, Middle, High
• Z-Dir Acc. – Cross-Bot, Cross-Top, Vertical
• Resultant Acc. – Low, Middle, High
• Resultant Acc. – Cross-Bot, Cross-Top, Vertical

Last Updated on November 22, 2019