1

Risk assessment for Benton County, Oregon, Part I: Using Excel for the first time

Benton County, Oregon, faces many hazards relatedto earthquakes. How can Excel help to quantify them?

Thomas Juster , Dorien McGee, Meghan Lindsey, and Len VacherDepartment of Geology, University of South Florida© 2010, 2011 University of South Florida Libraries. All rights reserved,

SSAChaz.TAJ.1(trad)

Core Quantitative Literacy TopicsFunction

Supporting Quantitative Literacy TopicsOrder of operations

Core Geoscience SubjectRisk assessment

The module you are viewing is a PowerPoint slide presentation.

•Navigate from slide to slide using the up/down arrow keys, or the scroll wheel on the mouse if one is available

•Use the mouse to select hyperlinks (underlined, in blue type)

•When done, use the escape key to exit the presentation.

You can and probably should have a spreadsheet open in a separate window, so you can try out things that are explained in the presentation.

PowerPoint applications use lots of memory, so you may want to exit other programs while running this presentation, especially if it starts to act slowly or sluggishly. If you don’t immediately see the slideshow when switching back and forth between windows, use the up/down arrow keys (or scroll wheel on mouse) to ‘wake it up’.

Close this window to proceed with the slide show.

2

Getting started

After completing this module you should be able to:

•Open, manipulate, and save an Excel file•Know how to use cell addresses in Excel•Understand the difference between text, numbers, and formulas in Excel cells •Use some simple Excel functions.•Use Excel to perform a simple risk assessment of Benton County, Oregon

Benton County

You should also know where Oregon is!

3

Overview: why use spreadsheets?

Geology, like other sciences, is a quantitative subject requiring some quantitative literacy. Numbers are necessary to express sizes, distances, lengths of times, rates of processes, and many other quantities. To learn geology, you must work with numbers. To some extent, you must think with numbers. In your Hazards of the Earth’s Surface course, some of your working and thinking with numbers will be done with spreadsheets.

Spreadsheets provide a convenient way to organize numbers and perform elementary calculations. You many not have had an opportunity to use and make spreadsheets before, or the time to learn how, so we are providing this module as a tutorial to introduce some of the basic Excel skills for the spreadsheet activities that will be used in this course.

Once you get the hang of spreadsheets, and how to calculate with them, you will find yourself using spreadsheets as your calculator of choice in other activities for this course, in others courses, and outside the University.

Important note: this activity assumes you are working in Microsoft Excel 2007. While you can do the modules in any version of Excel (2003, 2007, 2010), the instructions provided here pertain only to Excel 2007. If you aren’t using Excel 2007 you will have to learn the equivalent commands in your version.Excel 2007 is available on all USF campus computers.

Actual instructions are given in green boxes, like this one. When you see a green box you know you will have to do something that is part of your assignment.

All Powerpoint files (like this one) have an embedded Excel spreadsheet in them that you will open, save with your own unique name, complete as an exercise, and finally submit via Blackboard. So here’s your first task: click on the icon below to open the embedded spreadsheet file and immediately save with a unique name (e.g., “Joe_Smith-Unit-1”). (If you don’t save immediately, it won’t be there the next time you look for it!)

All your answers will be included in this file when you submit it; any spreadsheet calculations you do, as well as the text for any questions you answer. See the End-of-module Assignment.

All subsequent tables in this presentation will refer to this spreadsheet file.

4

How the spreadsheet modules work

Each of the spreadsheet modules you will do in this class has the same look and feel as this one. This consistency will help you know exactly what to do.

Blue boxes contain the content of the module. They give you the relevant background and explain how to perform tasks.

Supplementary, often important material will be shown in a red box like this one. Pay attention, because the red boxes often contain important Excel tips that will make your job easier.

Hazard-Risk-I

5

The setting – Benton County, Oregon

Benton County is located in western Oregon, close to a large earthquake-generating area called the Cascadia Subduction Zone. This zone is capable of producing gigantic earthquakes (perhaps akin to the 2004 Sumatra earthquake that produced the tsunami that killed 220,000 people), and poses the greatest risk to Benton County. Large earthquakes pose several kinds of risks:•They can topple buildings directly;•They can induce landslides;•They can literally turn sediment into quicksand, causing overlying structures to collapse

All photos from the Oregon State archives websiteFrom the USGS

Q1. What other states would be affected by large earthquake on the Cascadia Subduction Zone ?Go direct to End-of-module questions

6

Geology of Benton County

Benton County contains many different rocks and sediment. This is important because, as you will learn later, earthquake waves can be amplified when they pass through certain kinds of sediments, and can sometimes even induce ‘liquefaction’—the process of turning sediment into quicksand!

On a geologic map, different kinds of rocks and sediments are noted with different colors. The important thing to note on this map is the distribution of sediment (as opposed to hard rock), which is called variously “alluvium” or “deposits”.

Q2. Which part of the county is underlain by sediment, not rock?Go direct to End-of-module questions

7

Topography of Benton County

As seen in this Google Earth view, the western part of Benton County is rugged and largely unpopulated, while the lower, flatter eastern part contains most of the development.

Topography matters because steeper slopes are more prone to landslides.

Q3. Consider the risk posed by landslides in the context of the Risk Equation. What term mitigates against the higher hazard in the western part of the county?Go direct to End-of-module questions

8

Statement of the Problem

Benton County lies near the Cascadia Subduction Zone, which produces frequent earthquakes, and is capable of producing giant earthquakes. Benton County officials make decisions that are affected by this risk—for example, they create zoning laws which restrict activities in certain locations.

Your task: calculate the annual risk produced by a giant earthquake (magnitude M ≈ 9.0) for Benton County in terms of both (a) fatalities; and (b) monetary losses, in dollars.

This module will walk you through the completion of this task, and teach you how to use Excel along the way. The task is too big for one module, so has been broken up into two pieces. This is the first part.

9

An introduction to Excel: spreadsheet components

When you first open Excel, you are presented with a grid of boxes. This is the spreadsheet on which you will enter data. The file itself is called a workbook and is composed of several spreadsheets which can be accessed by clicking on the tabs at the bottom of the spreadsheet. You may add, delete, or change the name of tabs by right-clicking on the tabs themselves.

Above the spreadsheet is the toolbar, and above and below the spreadsheet is a series of command and formatting buttons. Many of these are identical to those used in Microsoft Word. Taking time to familiarize yourself with these first will make using them much easier later.

Spreadsheet tabs

Toolbar and buttons

10

Spreadsheet components, con’t.

Each box in the spreadsheet is called a cell, and each cell has its own unique alphanumeric cell reference. The cell reference begins with a letter, indicating the column in which the cell is found. The cell reference ends with a number, indicating its row. When you select a cell by clicking on it, the column and row to which it belongs will automatically be highlighted, making it easier to identify its cell reference. A field at the bottom-left of the toolbar also indicates the cell reference.

Cells are discrete units that can be loaded with text, numbers, or formulas. This tutorial will show you the various ways cells can be used.

Cell

Cell reference (A1)

11

Spreadsheet components, con’t.

Another key component of the spreadsheet is the function bar. Here is where you can enter a label (text), number, or cell formula (cell equation) into the highlighted cell (click on the cell to highlight it; the address of the cell is in the box to the left of the function bar). You may also type the label, number, or cell formula into the cell itself by selecting it and typing in what you want. If you enter a cell formula into a cell and it is correctly formatted, what you type will disappear and be replaced with the formula’s solution (a number); if it is not correctly formatted, the cell will show an error message. In either case, click on the cell and you can view the cell formula in the function bar.

Function bar

12

Spreadsheet components, con’t.

Solution to cell formula

Cell formula

13

An introduction to Excel: formulas

Entering a label or a number is straightforward. Simply type what you want. What you type is what you get.

Entering a formula is only slightly more complicated. The Big Thing is to remember to start with the equals symbol (=). Again, what you type is what you get. If you type and enter

=(2*6)+3

Excel will tell you what you get when you multiply 2 times 6 and then add 3, which is what the operation symbols in the formula say.

But if you omit the equals symbol, Excel will think you mean text and display (2*6)+3.

In our example of =(2*6)+3, the parentheses are unnecessary because multiplication takes precedence over addition (Excel multiplies before it adds). Enter =2*6+3 without the parentheses and check that you get the same result.

Q4. Sometimes the parentheses are very necessary. See what you get when you type the following:

=2+6*3 =(2+6)*3

Excel uses the rules for order of operations.

Excel uses the five standard number operations. Using the numbers 6 and 2, what numbers should be produced for each of the following five cell formulas?

For addition, =6+2 For subtraction, =6-2 For multiplication, =6*2 For division, =6/2 For exponentiation, =6^2

Check your answers with Excel

In these activities, we will always use orange to indicate a cell with a formula.

Go direct to End-of-module questions

14

Excel formulas, con’t.

Suppose you are given a table that categorizes all the structures in Benton County, Oregon. You want to compute the total number of structures in cell B10. In these

activities, we will always use yellow to indicate a cell with a number.

You can find the sum by typing in a formula that adds the numbers and places the result in cell B10 (look in the function bar).

But why do it that way? Use cell addresses instead of numbers! What do you get when you enter :

= B3 + B4 + B5 + B6 + B7 + B8 + B9

into Cell B10 or the function bar?

We will always use orange to indicate a cell with a formula. These should always start with =

15

Excel formulas, con’t.

It is an immense convenience to use cell addresses. Let’s take another example. Suppose we want to calculate the percentage of each type of structure in Benton County.

This column has already been created for you on your embedded spreadsheet, and labeled %. Note that Cell C3 is highlighted. The number shown in the cell is the percentage of buildings that are residential. We got that number by typing in the formula shown in the function bar, =B3/B10.

We could now go and type in formulas for all the other cells in column C; e.g., =B4/B10 for cell C4, etc. But in Excel there’s an easier way: we can copy the formula!

There are a couple of ways to copy a formula. Here is one of them: Select the cell containing the formula you wish to copy. Click the small black box in the lower-right corner of the highlighted cell (your cursor should turn into a thin black cross when you’ve in the right spot)….

…. and drag down until the box extends to the desired row. When you release the mouse button, your formula will be copied into the selected cells.

Yikes! What happened?!? Why do all the cells have errors (Excel’s way of telling you there’s an error is to use the # symbol)

Q5. To understand what went wrong, highlight any of the cells with errors and study the formula in the function bar. What’s wrong with the formula?

Go direct to End-of-module questions

16

Excel formulas, con’t.

This error highlights one of the key properties of Excel cell references: by default they’re relative.

For example, you may have thought that the formula =B3/B10 meant, “divide the contents of cell B3 by the contents of B10 . . . But that’s wrong! What it actually means is,

“Divide the contents of the cell one column over by the contents of the cell one column over and seven rows down”

Check the formula in this cell. Doesn’t it instruct Excel to do exactly what’s written on the left?

But that’s not what you wanted! When you wrote B10 you meant B10—always B10! In fact, what you wanted to tell Excel to do is:

“Divide the contents of the cell one column over by the contents of cell B10”

The solution in Excel is to use an absolute cell address, which is indicated by dollar signs ($) before the column and row identifier; e.g., $B$10.

Repair your formula in Cell C3, then copy it down to cells C4 through C9. Is it better now?

See Endnote 1 for other ways to copy and paste.

17

Excel formulas, con’t.

You may have been wondering how Excel knew to express the contents of column C as percents. After all, we just told it to divide whatever’s in column B by the contents in B10, which could have been expressed as a fraction; i.e., 0.91.

In Excel, percentage is a formatting option. (You should know that percentage is a fraction, with denominator equal to 100.)

To change the formatting of a block of cells, first select them using the mouse

Then click on one of the format styles. In this case we click on ’%’

You can adjust the number of decimal places by clicking on either of these butons

18

An introduction to Excel: functions

A function is one of the most important concepts in quantitative literacy. The quantitative literacy book Understanding our Quantitative World 1 starts with functions in its chapter 1. That book defines a function as a “rule” that, for each valid input, assigns one and only one output.

More succinctly, you can say y is a function of x, which you can write as y = f(x). The rule for this example then is given by f(x) = x/20992.

In math classes, you have probably seen functions written in terms of x’s and y’s. For example, consider y = x/20992Here, x is the input variable, and y is the output variable. You obtain a value of y by dividing the value of x by 20992. That’s the rule that defines this function.

1Janet Anderson and Todd Swanson, 2005, Mathematics Association of America.

Now consider what’s written in the function bar. Instead of writing y and x for the output and input variables, Excel writes C3 and B3, respectively. The rule connecting them is C3 = B3/20992Or, since 20992 represents the contents of cell B10, we can write the function as C3 = B3/$B10

19

Excel functions, con’t.

Your formula is a function!

In addition to functions that you can create such as f(x) = B3/B10, you can use many functions that Excel has built in. One of these is the function that totals a range of numbers.

The function that calculates the sum of numbers in a range is called—unsurprisingly—the SUM function. For example, we could have used the SUM function in cell B10 instead of having to enter all the numbers above into a long formula.

In Excel, a range of numbers is indicated with a colon (:) between two cell references. For example, the range of numbers that represents the number of structures of different type is B3:B9 (outlined in red).

This is how you use the SUM function: =SUM(B3:B9).

Use the SUM function to check that the percents you calculated in column C sum to 100%.

20

Excel functions, con’t.

Numerous other functions apply to fields of data. This spreadsheet shows four of them: =AVERAGE(B3:B9) =MAX(B3:B9) =MIN(B3:B9) =COUNT(B3:B9)

If you’re not sure how to use a function, select the cell with the function and then click on the Function button (fx) to the left of the function bar . . .

. . . and a window will appear telling you what the function does and what kind of arguments it takes.

Q6. What does this formula produce?

=SUM(B3:B9)/COUNT(B3:B9)Go direct to End-of-module questions

You can write formulas that do arithmetic with built-in functions.

21

Excel functions, con’t.

If you just want to explore the various functions available, click on the Function button (fx) while selecting a blank cell . . .

… and a window will appear that allows you to search for a function, select functions from a category, and shows you how you how to format its formula.

Click to select blank cell, then click fx

Q7. What does the function PRODUCT() do? Hint: this is a ‘Math & Trig” function; you need to choose this category

Go direct to End-of-module questions

22

End-of-module assignment

1. Answer questions Q1-Q7 on slides 5, 6, 7, 13, 15, 20, and 21.

2. The structures in Benton County can also be classified on the basis of their construction, as shown in the table below:

Create a spreadsheet table that computes the total number of structures, and the percentage for each construction. Your table should look very similar to the one on Slide 19.

3. What are the advantages of using a spreadsheet to do calculations as opposed to a calculator?

Type CountWood 17,050Steel 457Concrete 291Precast concrete 266Reinforced masonry 389Unreinforced masonry 290Mobile homes 2,249

23

1. Excel gives you myriad ways to copy and paste. Most people find one they like, and remember, and use it all the time. Here’s another way to do it.

a. Select the cell you want to copy (position the cursor on it and click);b. Then right-click and select ‘Copy’;c. Now position the cursor where you want to copy (which might include selecting a range

of cells); andd. Right-click and select ‘Paste’

Go back to Slide 16.

Endnotes