The Spreadsheet

 

Definitions

Cell = The box that contains each number, where columns and rows intersect
Column = The vertical arrangement of data identified by letter
Row = The horizontal arrangement of data identified by number
Entry bar = field where you set the equation for a selected group of cells

Basic Equations and Functions

Addition: =a1+a3+a4
Sum function: =sum(a1,a2,a3,a4) or =sum(a1:a4)
Multiplication uses asterisk as multiplication sign: =a1*a5.
Product function: =product(a1..a5) means =a1*a2*a3*a4*a5
Division uses / for a division sign, like fractions: =a2/a4
Exponents: =a1^2 squares the content of cell a1; also can be written =power(x, y) for xy
Exponential notation: 2e-1 is 2 x 10-1 and 7e6 is 7 x 106
Average: =average(a1:16) calculates the mathematical mean of the range
Minimum: =min(B1:B205) finds the minimum value within a range of values
Maximum: =max(B1:B205) finds the maximum value within a range of values

Sorting

1) Select the data by clicking and dragging to highlight the desired area.
2) Choose the Sort... function from the Data menu. Notice Excel lets you sort by non-selected header as well as by row/column ID.
3) The Options button lets you change to sort by column (horizontally; left to right).
4) Click the OK button.

In-Class Exercises

Oceans

Build a spreadsheet with this data:

Columns:
Ocean
Area (sq km)
Deepest point
Depth (metres)

Data:
Pacific; 155,557,000; Mariana Trench; 10,924
Atlantic; 76,762,000; Puerto Rico Trench; 9,219
Indian; 68,556,000; Java Trench; 7,258
Southern; 20,327,000; South Sandwich Trench; 7,235
Arctic; 14,056,000; Eurasian Basin; 5,122

Earth's total water area is 361,419,000 sq km, which is 70.9% of the planet's surface.

Answer the following:
What is the mean area of all the oceans?
What is each ocean's percentage of the Earth's water area?
What is the mean deepest point of all the oceans?
What percentage of the earth's water is ocean water?
Together, the Atlantic and Pacific make up what percentage of Earth's ocean area?

Sample Files

To download these files to your computer, hold down the Control key while you click the link. Choose "Download Link to Disk" or the equivalent choice in your browser. That will download the file to your hard drive. Try to save the files in the Storage volume if that's not already the default. These are Microsoft Excel files unless otherwise noted.

MortgageRates

citypop - Of the world's most populous cities in 2002, which will experience the most growth in absolute numbers from 2002 to 2015? The least growth? What about by percentage?

Baseball - These are simple numbers, but let's see what we can tease out from them. Did mean payroll rise or fall from 2001 to 2002? Which team had highest absolute payroll rise between 2001 and 2002? Percent-wise?

RD2002 and RD2003 - These spreadsheets from Technology Review contain the revenue totals and research and development budgets of the world's major corporations.

Forbes 400 Richest Americans 2002 (Excel, and .txt versions)

How to Choose a Chart or Graph

Use bar charts (histograms) to compare multiple values within a unique time frame or a single value at defined points in time. Only one of your data ranges should be variable, be it time or cost or number.

Examples are:

Scatter plots are best used to display the relationship between two sets of variables.

Pie charts best represent several values’ proportions of a whole, such as:

Line and area charts should be used to plot the change of one or more values over a continual range of another value, most often time. Each line in a graph can plot a single variable.

Making a Chart or Graph

Microsoft Excel is much, much more flexible and easier to use than AppleWorks, which will only chart/graph adjacent columns and rows.

First thing you need is data. We'll use this file, of simple and real estimates of the percentage of installed copies of Windows that are pirated in a variety of nations, along with the cost of Windows indexed to each nation's own GDP.

In Excel, drag and highlight the columns of data to chart. Select "Chart..." from the Insert menu and choose to make a scatter chart (for our example).

The next window shows you your data range in a code-like jargon. Here, you can change the range of data to be charted if you want to. You can also choose to establish your series by column or row. A series is a range of associated values. Look at the scatter plot above. In plain English, in that chart, there are two series: one is the set of all the percent of piracy; the second is the set of all the values of cost in GDP. Almost always, you will have your series arranged in columns, but you need to make sure you select the proper series for the X axis. To do this, click the Series button at the top and alter this manually in code. It's not complicated - just switch the column ID letters - change all Bs to Cs and vice versa, for example.

Once your series are set up, you can lay out the chart with labels, gridlines, axis titles, etc. Go nuts. After you're bored with that, you can choose to display the chart on the spreadsheet you are working on or on a fresh, blank spreadsheet.

After a chart is created and displayed, you can resize it and its elements using the control handles (black squares) at its corners. Play around and double-click to get/remove labels and titles. You'll get the hang of it with practice. One cool thing is that you can also copy/paste a chart from Excel into graphics or text docs.

Case Studies

The first two stories at this page rely entirely on spreadsheets and data analysis to investigate a story.

Here is the spreadsheet of hunter injuries used to write these articles.

Here is an ugly but telling scatter graph of the 2000 US Presidential ballots in Florida. But what does this graph tell you, exactly?