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

• Cells: each one holds a single datum which can generally be a number, text, a variable, or a reference to another cell.
• Sheet or worksheet: Array of cells
• Automatic recalculation and redrawing: the ability of the spreadsheet to recalculate everything each time there is a change and draw the update is a great advantage.
• Cell contents can be formatted in numerous ways
• Cell referencing: relatively letter_number of cell (=A1) or absolutely ($letter$_number of cell) (=$A$1); can also point at a different sheet: =SHEET2!A1 (the first cell in sheet 2 of the same spreadsheet)
• Formulae: =expression, where the expression can be any combination of logical or mathematical operations on constants, variables, or cell references
• Charts: can make many graphics that depend on cell contents

Limitations

• Sometimes hard to audit and error check
• Hard to change dimensionality once you start (how big should the array be?)
• Limited size (Many versions are limited to < 64,000 cells)

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.

Example of some simple graphical operations

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.

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: