You Need
A zip disk or memory stick 

Lab Four

Complex Data Analysis in Microsoft Excel 

Preliminaries

In this lab we will use a spreadsheet to perform a complex data analysis task involving experimental data, numeric integration and statistical analysis. 

 

Home Page

Lab Page


 

Lab Resource Links

 

To Do

1. Start with the raw data

Follow  this link for PC to open a Microsoft Excel spreadsheet containing raw experimental data.  Immediately save this file (using Save As...) to your memory stick or U: drive.  Modify the file name to some version of your name that uniquely identifies this file as your work, for example AshKetchumL4.xls. 

2. The analysis task 

Two manufacturers supply you aluminum alloy fasteners at slightly different prices and your company wants to determine if there is a significant difference in the strength.   Typically, aluminum alloys deform along the stress strain relationship shown below.

From Beer and Johnson, Mechanics of Materials, 2nd.ed.
The vertical axis measures the stress, in Newtons per square inch, or Pascals (Pa) and the horizontal axis measures strain in mm per mm (dimensionless).  Stress is a measure of the pressure experienced by the material and the strain a measure of the deformation due to that stress.  Before the yield stress (sY) is reached, the stress and strain exhibit a linear relationship.  The slope of the linear region is Young's Modulus (E), a measure of the relative stiffness of a material that varies for different materials and alloys. For example steel has a higher Young's Modulus than aluminum, and so we say steel is "stiffer" than aluminum, meaning it stretches less than aluminum when subjected to the same stress.  A materials "toughness" is the entire area under the stress-strain curve, and this is a measure of the total energy the material can absorb before fracture. 

To experimentally determine the fastener's toughness  we place it in a tensile testing machine and stretch it until it breaks.  The machine has direct sensors for force and displacement and these data are logged for the entire test. We are interested in stress vs. strain so we must process the raw data into a usable form.  All experimental data is subject to measurement error due to a variety of causes, and we refer to fluxuation in experimental data as "measurement noise".  We can see that the experimental data contains noise and want to minimize the effect of this noise.  In this case we want to ignore force data that is recorded after the apparent break of the sample, as these measurements are merely noise and not indicative of anything about the sample. 

We can not draw conclusions from a single sample, so we destructively test ten fasteners from each manufacturer.  We then compute the toughness of each and analyze the data to determine if there is a statistically significant difference between the toughness of each type of fastener.
 

3. Steps to data analysis 

The Microsoft Excel file contains three worksheets.  The "check" worksheet contains a single trial for a fastener of type 1 and is identical to the first trial in sheet "Alloy 1".  The other sheets contain data for ten trials of each fastener type.  A blank worksheet named "t-test" is also provided. Perform the following steps to complete the data analysis.
  1. On the "check" sheet manually calculate the stress and strain for the data set by using the following formulas:
 

                                                              

        e= strain                                               s=stress

        d=elongation                                     P = axial force

        L= original length                              D=sample diameter

         
    1. Plot stress vs. strain for this single sample
    2. Manually truncate the data after the apparent break
    3. Use the trapezoid rule developed in lab 3 to calculate the area under the curve up to the break
    4. Record the total area as the toughness of that sample.
  1. On the "alloy" sheets, repeat the calculation for each sample producing "toughness" measurements for each sample of both alloys, for a total of twenty samples.
  2. Calculate the mean and standard deviation of the toughness of the ten trials on the "alloy" sheets.
  3. Hint: copy the completed formulas on sheet "Alloy1" to sheet "Alloy2" in their entirety to avoid duplication of effort.
  4. On the "t-test" sheet, load the Analysis Toolbox and use the TTEST function to determine the answer to the following question.
  5. Write the answer to the following question: "Can you say with DD.DD% certainty that fastener type 1 is stronger (or weaker) than fastener type 2?".  Where DD.DD is your customization code, the first two digits of to the left of the decimal point, the second two to the right.  For example if you customization code is "4215" the answer the question "Can you say with 42.15%  certainty that fastener type 1 is stronger (or weaker) than fastener type 2?"and write the justification for your answer.

Hand In

Submit the spreadsheet via BlackBoard.  Please make sure your name and customization code are on the top of the sheet. This lab is worth 200 points.

Opting-out

Please turn in a completed spreadsheet with all of the features listed above using Microsoft Excel or some equivalent spreadsheet program with your name and other information in a header. If you do not use Microsoft Excel, you must demonstrate proficiency in that program by turning in the completed analysis. 

 [ENGR115 Home Page] [ENGR115 Lab Page]

Maintained by Matthew Stein
February 24, 2009