Excel Lab

Learning Objective:

The learning objective for this lab is to give you hands on experience with the Excel spreadsheet program. Specifically, given data in a tab delimited text file, use Excel to calculate simple descriptive statistics and plot the data.

Outline:

Your task is to get the data in the text file into Excel, and then use Excel built in functions to calculate the average and standard deviation for each of three columns of numbers. This is the primary learning objective. Once you have accomplished this, you will then try various graphic options in excel to visually display the data in plots. If you complete these tasks your last objective is to find the trend line that best approximates the data.

Have fun.

Procedure:

Get the data into the spreadsheet

  1. download the tab delimited data file
  2. Open the file with notepad by double clicking; examine the data to get a feel for it.
  3. Close notepad
  4. Open Excel
  5. Open the text file "Data1.txt" Menu File/Open (Files of Type Text Files), a series of screens will allow you to parse the data.
  6. Save the Workbook as an Excel Workbook Data1.xls

Calculate Descriptive Statistics

  1. Highlight the cell two cells below the bottom of the first column of data "x"
  2. Insert the average function; make sure the range of arguments for the function corresponds to the actual x data.
  3. In the next cell below your average calculation insert the StdDev, standard deviation function.
  4. Repeat this procedure in the next two columns to calculate the average and standard deviation for the y1 and y2 data columns.

Plot the Data

  1. Select the x data, then click on the Chart Wizard icon on the Excel tool bar. Explore the different plots you can produce.
  2. Create an "xy" type chart as a separate sheet.
  3. Examine the chart. What can you tell about the range of the data?
  4. What is the independent variable in the plot?

Add Trend line and Equation

  1. Go to the chart you created and right click the mouse on the data points. Select "Add trend line" from the popup menu. In the Type tab select Linear, then in the Options tab check the "Display equation on chart" box. The result will be a best-fit trend line for the x data. What is the value for the intercept in the trend equation? How does this compare to the average you calculated on the worksheet.
  2. Repeat the charting and trend line for y1 data.

Last Item

Repeat the charting and trend line again, but this time select both x and y1 data. The xy charting will plot the data as a collection of points with x coordinate from "x" column and y coordinate from "y1" column.

Can you explain the relationship between x and y1?