Excel – Lab (2)

Visualization and Computing with Excel


Before we start working on this laab, let's go back to the data you have created yesterday for the distance and elevation of the twons you had come from. In the table you had for the elevation, add two more columns, one for Latitude and another for Longitude of your town. It is best to have these two columns before the Elevation. Generate a plot that has the Latitude and Longitude on its x and y axes and the elevation on its z axis. Thus, generate a 3-D Surface Grapgh with the three columns of data you have.

Graphing and Curve Fitting

To graph data in Excel, enter your data one variable per column in consecutive columns. Highlight the data you wish to graph and then choose Chart... from the Insert menu. This brings up the Chart Wizard. There are many graph formats to choose from; once you choose one, the wizard will walk you through customizing your graph. Here is an example of bar charts.

 

 

A few items to note while working through the chart wizard:

 

            Most scientific graphs show the relationship between two quantities. In Excel, such a relationship is graphed using a scatter plot. Use a blank sheet by clicking on a Sheet at the bottom.  Type in the independent (x) and dependent variables (y) in adjacent columns; Excel will assume that the left column is intended for the horizontal axis. Graph a scatter plot of these points by highlighting the data and then clicking on the chart wizard Be sure to choose scatter plot and not line graph. Line graphs assume that the horizontal axis should be marked with counting numbers (1, 2, 3, etc.) and that each highlighted column is a separate set of vertical axis coordinates.

            Read the directions in the wizard to customize your graph. After you have done all of the steps, you should see a plot of your data.

 

Picture 2            

 

Fitting Curves to Data

Often scientists look at scatter plots like the one above to determine the trend of the data by finding a function that fits the data well. Excel has Least Squares Regression routines built in for the most common behaviors: linear, polynomial, exponential, power, logarithmic.

            To fit a curve to the data in the chart above, first graph it as a scatter plot. Now you are ready to add a fit.  Click once on any part of the chart, and then choose Add Trendline from the Chart menu. You will be asked what kind of trend. Also set the options so that the formula for the fit and the r-squared value are printed on the graph if desired.

 

Picture 3                        Picture 4

 

 

The curve will appear on the graph along with the data.

 

 

            As always, you should use caution when interpreting the r-squared value. Mathematically this value must increase for “curvier” functions such as polynomials of higher degree. Choosing between different functions should be done visually, not relying solely on the r-squared value.

 

Lab Activity 2:

For the following questions, write your answers to all four questions in a file and e-mail Jere the file at the end. Or write your answeres in the body of your e-mail and send the e-mail. Note that there is a data file (Datafile) under each of the links that you will you can use in your analyses.

(graphing 1) Use linear regression to find a formula relating the amount of the speciman to the output from the gas chromatograph in the dataset.

http://lib.stat.cmu.edu/DASL/Stories/Chromatography.html



(graphing 2) The inverse of Hubble’s constant can be used as an estimate of the time since the big bang. Use the data to fit a curve to find Hubble’s constant (note that the relationship has no intercept).

http://lib.stat.cmu.edu/DASL/Stories/Hubble'sConstant.html



(graphing 3) Investigate Gompertz’ law for population growth.

http://lib.stat.cmu.edu/DASL/Stories/Gompertz'sLaw.html



(graphing 4)   Investigate the relationship between mortality from breast cancer and average annual temperature..

http://lib.stat.cmu.edu/DASL/Stories/Breastcancer.html

 

note that the last one is always constant because it is not time-dependent.