GLG410--Computers in Earth and Space Exploration

Announcements Syllabus Schedule Weekly lecture notes Assignments Links

Lecture 3

Addressing Scientific Questions with Spreadsheets

Spreadsheet is a powerful computational engine.

"A spreadsheet is a computer application that simulates a paper accounting worksheet. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns. Each cell contains alphanumeric text, numeric values or formulas. A formula defines how the content of that cell is to be calculated from the contents of any other cell (or combination of cells) each time any cell is updated. Spreadsheets are frequently used for financial information because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made. A pseudo third dimension to the matrix is sometimes applied as another layer, or layers/sheets, of two-dimensional data."

Another definition: 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.

The web is full of resources on spreadsheets. From the very basics of simple video introduction to humor (spreadsheet jokes). You may ask, "Why Excel?" 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. And there are spreadsheets in GoogleDocs Sheets. So when we say "Excel" in this lecture and subsequent ones, we mean "spreadsheets."

Important concepts


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

Example of some simple graphical operations

Pull some data off the web and plot it in Excel. Let's look at the 2011 Virginia earthquake aftershocks. Start at this USGS web site:

Format the plot:

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. Highlights very basic plotting and also spreadsheet and plot formatting.

Creating X-Y Charts XYChartExample.xls. Highlights basic computations including the use of names (variables), filling cells, and check the result by fitting a trendline.

and QuadraticFormula.xls. Highlights basic computations, including the use of names (variables) and filling of cells.

Creating contour and surface charts EllipticParaboloid.xls. This one shows semi-relative referencing and a surface plot.

Changing the axis units
Creating a title
Creating a legend
Creating axis labels

Parsing Text Files and converting text to columns 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 link below shows earthquakes for the last day >M2.5. Click on the download csv to pull data into Excel.

Recent Earthquakes

Formatting Numbers in Excel

Specifying number format
Specifying number of significant digits in problem

Challenging example

Rainfall data: Pull some data from the Maricopa County Flood Control District rainfall sensors and plot it. Those shows a challenging formatting, parsing, and date handling application:

GLG410/598 Computers in Earth and Space Exploration

Last modified: September 2, 2015; some of this web page was built by George Hilley and Ed Garnero.