USING SPREADSHEETS:



COMPUTER DATA ANALYSIS using EXCEL

(Follow-up to Measurements Lab)

PURPOSE:

The purpose of this lab is to develop computer data analysis techniques that you may draw upon later in this course. You will learn how to determine the mean and standard deviation, how to generate charts and plots, and how to work with spreadsheets in general. We will use the most popular spreadsheet program available in industry to best prepare you for available tools in the real-world working environment. If you can do it in Excel, you can do it anywhere!

DATA ACQUISITION:

Using either the micron calipers or the vernier calipers, measure the length of 10 similar screws and determine the “plus or minus” and “percent” error in their lengths. Remember that although you may be able to read the length of an individual screw to very high precision, the “plus or minus” error we are looking for should represent the full spectrum of measurements for the 10 different screws.

Next, you will input your data into an EXCEL spreadsheet that will help you determine the average, xavg, and the standard deviation, σx, of your measurements.

USING SPREADSHEETS:

PART 1: USING EXCELL TO GENERATE DATA TABLES

Enter your data according to the instructions and demonstrations given in class. Keep in mind the following suggestions.

1) Enter your raw data in a single column or in adjacent columns (columns next to each other). Use a descriptive header at the top of each column. You must use your own data from the previous lab. You will learn the most if you use the very data you originally took even if it is not perfect. It’s not how accurate the data is, but rather the data analysis techniques that are important here.

2) Create additional columns that will reproduce your original data analysis for this lab. Feel free to improve on your original efforts. Don’t leave anything out, be as complete and thorough as possible. The goal here, is to reproduce your original work from the previous lab, but all on the computer.

3) Use an = sign to write in the formulas to each column, then copy the formulas down the column. Have someone show you how this is done, or use the help feature of Excel to help you. Cells will be relatively referenced unless you lock a row or column with a $. The instructor will demonstrate this.

4) To add up a column, use the following formula “=average(cell range)”, where cell range is input dynamically as shown in class.

5) Reproduce all the required data tables as per the original lab.

6) Do your results in Excel match those you got by hand in your previous data tables?

Here is an example of the columns that you should have for the “Measurements Lab”. Remember, all calculations must be calculated in excel, not copied into the table from your lab book. Only copy the raw data into the table.

|n |Xi |Xavg |Xi-Xavg |(Xi-Xavg)2 |

|1 |4.5 |4.5 |0 |0 |

|2 |4.4 |4.5 |-0.1 |0.01 |

|3 |4.6 |4.5 |0.1 |0.01 |

|4 |4.3 |4.5 |-0.2 |0.04 |

|5 |4.2 |4.5 |-0.3 |0.09 |

|6 |4.7 |4.5 |0.2 |0.04 |

|7 |4.8 |4.5 |0.3 |0.09 |

|8 |4.5 |4.5 |0 |0 |

|9 |4.5 |4.5 |0 |0 |

|10 |4.5 |4.5 |0 |0 |

| | | | | |

|Sum(Xi)= |45 | |Sum= |0.28 |

| | | | | |

|Avg(Xi)= |4.5 | |STD= |0.176383 |

PART 2: USING EXCEL’S STATISTICAL FUNCTIONS and FEATURES:

In this part of the experiment, you will generate the statistics of "mean" and "sample standard deviation" directly from the raw data.

1) Begin by copying the raw data from the above table to another location on your spreadsheet. Only copy the raw data. To dynamically link the copied data to your original data, in the first cell of data type “=” then click in the first cell of the original data, then hit the return key. Copy this formula down through the rest of your column. Now if you change a value in the original data, the copied data will follow suite.

2) At the bottom of the column, skip a line, and calculate the mean by using the following formula "=average(cell range)", where cell range is determined dynamically as demonstrated in class.

3) Skip another line, and then calculate the sample standard deviation by using the following formula "=stdev(cell range)", again, where the cell range is determined dynamically as demonstrated in class.

Here is an example of what your EXCEL table and statistics should look like:

|Using Excell | | | |

|Statistical | | | |

|Functions: | | | |

|(Using data | | | |

|from | | | |

|Measurements | | | |

|Lab) | | | |

| | | | |

|n |Xi | | |

|1 |4.5 | | |

|2 |4.4 | | |

|3 |4.6 | | |

|4 |4.3 | | |

|5 |4.2 | | |

|6 |4.7 | | |

|7 |4.8 | | |

|8 |4.5 | | |

|9 |4.5 | | |

|10 |4.5 | | |

| | | | |

|Avg(Xi)= |4.5 | | |

| | | | |

|STD= |0.176383 | | |

PART 3: USING EXCEL TO GENERATE BAR CHARTS

Follow the instructions below to generate a bar chart of your data as demonstrated in class.

1) First highlight your data, including the label at the top of the column.

2) Select "Insert", then "Chart", or simply click on the shortcut icon for "Chart" in the toolbar across the top of your screen.

3) Select "Column", then the "Chart Subtype" of your choosing. What subtype would be appropriate for this single column data set? You can click on "Press and Hold to View Sample" to see what the chart will look like with your data. Select a 3-D bar chart, then click "Next".

4) Check to see that the series are in "columns", then click "Next" again.

5) Modify the "Chart Title" and variable names, if you wish, then click "Next" again.

6) Select "As an object in" the spreadsheet, and click "Finish".

7) As demonstrated in class, move the chart to where you want it, and adjust the size as you wish.

8) Right click on the gray shaded area, and select "clear" to prepare the chart for proper printing.

9) See if you can now generate a "Bar Chart". The only difference will be that after you activate the Chart Wizard, select "Bar" instead of "Column" for the Chart Type.

10) Now that you are all done with the statistical data analysis, print the entire spreadsheet and staple the results to your lab report.

Here is an example of the Bar Chart generated.

EXTRA CREDIT: GENERATING A NEW SET OF DATA

Now that you have completed the spreadsheet analysis, you can use it to immediately analyze a new set of data without having to do any additional work.

1) Measure ten nuts, bolts, or screws that are different than the ones you measured in the previous lab. You should use a different measuring instrument than last time as well. If you used a Vernier Caliper last time, you should use the Micrometer. If you used the Micrometer last time, you should use the Vernier Caliper this time.

2) Delete the data in your data table of your spreadsheet, and input the new data. If you did your data table right, you should only need enter your raw data in one place and all other calculations in your spreadsheet should automatically update.

3) Check that all your data automatically updated correctly. Print and staple to your lab report.

CONGRATULATIONS:

What is the best estimate for the length of the screws? The best estimate of the length should be written as xavg ( σ. Statistical theory states that if you measure another screw, there is a 68% probability that it will be within 1 standard deviation of the mean, a 95% probability it will be within 2 standard deviations of the mean, and a 99.7% probability it will be within 3 standard deviations of the mean. The mean “plus or minus” one, two, or three standard deviations, is referred to as a confidence interval. Be sure to include the above sentence, and/or give the confidence intervals for your data, but calculate with your own numbers.

You are now a computer literate data analyzer. I hope you will draw upon these newly developed skills to enhance your lab-work in this class and others

-----------------------

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download