Lecture 3: Addressing Scientific Questions with Spreadsheets

Introduction

What is a spreadsheet? What is it good for?

A spreadsheet is a bunch of information organized in tables of columns and rows. This information, commonly numbers, text, and formulas can be be utilized quite powerfully in science and engineering. The Windows operating system's program Excel is a tool we'll explore over the next several lectures. There are many, many texts available for getting the most out of Excel for science and engineering purposes. "Excel for scientists and engineers" by William Orvis from Amazon is a nice book, and there are dozens of others.

A starting point.

The web is full of resources on spreadsheets. From the very basics of simple definitions to humor (spreadsheet jokes). Our local community colleges offer resources as well (e.g., an MCC course). You may ask, "Why Excel?" This is a good question. In fact, if you were going to be using a UNIX machine for the rest of your waking life, I would spend this time instead teaching you how to code in Fortran or C. But the fact of the matter is, most of our culture has PCs or MACs. And these boxes already have Excel on them. And on top of that, Excel is quite powerful, as is. Note also that you can find basically the equivalent functionality in Open Office and it is free and runs on Mac, Windows, and Unix/Linux. So when I say "Excel" in this lecture and subsequent ones, I mean "spreadsheets."

Let's open up Excel. There are two easy ways to launch the software: (1) inside of a folder you want the file to end up, right click the mouse, selecting New, then select select Microsoft Excel Worksheet; (2) Go to the Start menu, select Programs, then choose Microsoft Excel. Once you've opened the Excel application, here are some of the spread sheet basics, which applies mainly to the beginners.

How are spreadsheets used in science?

Answering this question is quite similar to answering: "how are computers used in science?" This owes to the fact that Excel is capable of doing many of the things that are accomplished with complicated computer programs on large computers. Thus spreadsheets are used in a wide variety of applications in science, as we will shortly see.

By the way, Excel has been around since 1985: John Walkenbach's Excel pages.

Basic Spreadsheet Commands

Entering Numeric Values. Very easy: click the cell where you want to enter data and type the data and press ENTER or TAB.

Entering String Values. With text functions, you can manipulate text strings in formulas. For example, you can change the case or determine the length of a text string. You can also join, or concatenate, a date to a text string.

Entering Equations Click the cell in which you want to enter the formula, type = (an equal sign). Enter the formula. Press ENTER.

Naming cells, formulas, constants. In Microsoft Excel, you can name a single cell or a range of cells to make formulas easier to read and remember. If you have row and column labels on a worksheet, you can refer to them directly in a formula, or you can use them as names that represent the associated cells. You can also create a name that represents the same cell or range of cells on more than one worksheet. Use the Insert->Name->Define to define a variable and use Insert->Name->Paste to use it.

Referring to other cells In Excel you can refer to a cell or a range of cells on a worksheet and tell Excel where to look for the values or data you want to use in a formula. With references, you can use data contained in different parts of a worksheet in one formula or use the value from one cell in several formulas. You can also refer to cells on other sheets in the same workbook, to other workbooks, and to data in other programs. References to cells in other workbooks are called external references.

Absolute vs. Relative References A given cell can contain a formula that references either relative locations to the cell with the formula (e.g., two cells to the right and one cell up), or an absolute reference to some specific cell -- irregardless of the position of the cell containing the formula. If cell C3, for example, contains a formula with reference to E9, then that is a relative reference. If this formula is copied to cell C4, then it will refer to E10. For absolute references, the same cell C3 pointing "absolutely" to E9 would have the syntax $E$9. Now if the formula is copied, it will still point to that specific cell E9.

Inserting a function Excel has many built in functions (like trigonometry, summation, etc.). Find them under Insert->Function.

Basic Operation of Microsoft Excel to Understand Geologic Problems

We will explore various spreadsheet examples, and see the utility of Excel for everything from simple graphs of (X,Y) pairs of data, to interactive programs that let you see how the stress field near a magma chamber or conduit will change depending on the ambient tectonic stress of in the area. You will soon see how Excel can be easily used to analyze physical processes of all types. You can swiftly swipe data from a table from the web, paste it in Excel, and, varooooom, you have a plot of it, a best fit line, etc etc.

Visualizing Data-- Plots in Excel

For the following, highlight the cells for which you wish to make a plot, then click on the chart wizard. The following bullets will make sense as you choose different options.

Creating Bar and Pie Charts BarChartExample.xls
Creating X-Y Charts XYChartExample.xls and QuadraticFormula.xls
Creating contour and surface charts EllipticParaboloid.xls
Changing the axis units
Creating a title
Creating a legend
Creating axis labels

Parsing Text Files in Excel

What does parsing do? This is the action of inserting or replacing a character (or characters)in a table of information. This is useful: you can copy a table of text from your browser window, paste it into Excel, then tell Excel to replace spaces of commas with tabs, or to use the commas in a file (for example) as the character for which columns are to be created. (see data, text to columns).

Some examples The U.S.G.S. has many tables of information about earthquakes that we can play with. The links below are for past historic California earthquakes, and number of earthquakes greater than magnitude 7 for this century. You will utilize with these in an assignment.

Past Earthquakes
Recent Earthquakes

Formatting Numbers in Excel

Specifying number format
Specifying number of significant digits in problem

Spreadsheet links

Spreadsheet Assignment

     <a href="filename.xls">filename.xls (or whatever you want the link text to say)</a>

1. Excel warm up exercise
2. Excel warm up exercise 2
3. Excel warm up exercise 3