Indiana University Multi-campus Modular Computing Curriculum

Introduction to Computing

Module ^{#} 7: Spreadsheets

Goals

• Enter and edit data in cells of a spreadsheet.

• Use simple formulas

• Cut/copy/paste data and formulas

• Give names to ranges of cells, and use those range names in formulas

• Create a spreadsheet to represent numeric dependencies for simple problems

• Use a spreadsheet to answer “what if” questions about simple problems.

• Create a simple graph from a number of data points

Prerequisite Skills

• Familiarity with normal file and editing operations (opening/saving files, using the mouse, etc.)

• Some familiarity with word-processing concepts

• Basic arithmetic including familiarity with fractions and percentages at the grade-school level.

1 Discussion

1.1 History Spreadsheets are, in many ways, the application that launched the personal computer into the business world. The very first spreadsheet, VisiCalc, was created for the Apple II line of computers and was an immediate success. Lotus was the first company to bring the spreadsheet to the IBM

Module #7: Spreadsheets

7-1 PC compatible with Lotus 1-2-3, and other vendors quickly followed with their own versions of the application.

Spreadsheets are more popular than ever today, often being found as the cornerstone of what People Who Like Big Words call “Administrative Support Systems”. Even a simple spreadsheets give a person enormous power to keep track of how money and information flows through a company, or to rapidly try out several values (for a product price or a test curve, for example) and see the ramifications of each one.

Spreadsheets, more than anything else, were at the heart of the first “look and feel” lawsuits. As more companies made spreadsheets, the question came before the courts: Can you copyright or patent the ’look and feel’ of a program – the way it appears to the user and the keys you need to press – even though other people are writing their own programs from scratch? The answers have been ambiguous and mixed over the years, but the general idea currently is that you can copy another program’s “look and feel” (within reason) without getting sued.

1.2 What is a spreadsheet?

A spreadsheet is a way to set up relationships between numbers.

Sure, a spreadsheet will make graphs and do arithmetic and create really good-looking tables, but most of that is just snazzy extras. The heart and soul of a spreadsheet is its ability to let you set up relationships between sets of numbers, and then play with the numbers to see what happens.

For example:

• Interest earned is 4% of the current balance per year

• The final car price is the dealer price + cost for extras + 10% commission

• My checkbook balance is the amount of the initial deposit + all the deposits since then - all the withdrawals since then

By setting up correct relationships between the different values, we give ourselves the ability to “play” with the actual numbers to see what happens.

1.3 “What if...” questions

Spreadsheets are most powerful when we’re using them to answer “what if” questions. Since we’re setting up relationships between numbers, we can change a single number and see what it does to all the other numbers.

Before spreadsheets, when all this sort of information was kept on paper, you couldn’t change things around or answer questions without an enormous amount of work. Now, a couple keystrokes can give you an answer in less than a second.

For example:

• The Fed is hinting that they’ll raise interest rates 0.5%. What will that do to our investments?

7-2 • How many more fish will survive next year if we cut toxic dumping in the lake by 5%?

• The minimum wage is going up $0.75/hour next year. How will that effect our salary payments, and what do we need to do to offset the change?

1.4 Problems that lend themselves to spreadsheets

• Any problem that involve a lot of numbers, many of which are dependent on each other or are likely to change.

• Anything you might need to graph, since all spreadsheets have a graphing program built into them.

• Any problem where you need to play with numbers in order to find an “optimal” solution.

1.5 Problems that spreadsheets aren’t good at solving

• Anything involving a lot of text. Spreadsheets don’t do text well.

• Problems with significant database activity. While most spreadsheets can do limited database operations, most of the time you’re better off (surprise!) using a real database.

• Any “real”, heavy-duty statistics. Most spreadsheets can do the necessary operations up to a couple thousand data points, but when dealing with several thousand data points in complex patterns, you should really use a dedicated statistics package.

2 Spreadsheet Concepts

2.1 Cells

A spreadsheet is made up of lots of little boxes called *cells*. Each cell can hold a number, some text, or a formula which performs some arithmetic on numbers.

Each cell refers to a specific spot in memory.

Each cell also has a unique address; cells are addressed by a column-letter and a row-number. If you’re familiar with the old game “Battleship”, you’ll be quite comfortable with cell addressing.

A B C D
1 A1 B1 C1 D1
2 A2 B2 C2 D2
3 A3 B3 C3 D3
4 A4 B4 C4 D4

Each cell has a particular data type, depending on what’s in it. Although it looks as if there are a ton of different data types, there are really only three.

7-3 Text A cell might contain some words, or a single word, or a single letter. All these are lumped

together under the heading of simple text. All text has a numeric value of zero!

Numbers A cell will most often contain a number. The problem is, it might not look like a number.

You can format numbers to appear as currency ($), percentages (dates and times. All these and more are represented inside the computer as a number. Dates can be especially tricky, because at first glance they look like they’re text.

Q. 1 Why pretend dates are numbers? Why not just use text?

(Answer on page 7-26.)

Formulas A formula is a mathematical expression that refers (most often) to other parts of the spreadsheet. Using formulas, you can set up those mathematical relationships that are used to keep track of things and ask those “What if...?” questions. We’ll talk a lot about formulas in a little bit.

Q. 2 Hmmm.... text, numbers, and commands (formulas). Haven’t we seen all this before?

(Answer on page 7-26.)

2.2 Ranges

Any rectangular group of cells is called a range. Ranges are useful in various formulas that can work on a large number of cells at a time (e.g., the sum function, which adds up the values in all the cells in a given range).

Ranges are defined by giving their upper-left-hand corner cell address followed by a colon on the lower-right-hand-corner cell address.

Sometimes you will see ranges with .. between the cells: EG (A1..A10), (B5..C9).

A1:A10 The first ten cells in column A

A1:D1 The first four cells in row 1

A1:B3 The first three cells of columns A and B (or the first two cells of each of the first three rows, depending on how you look at it).

B1:B1 A very small range – just the one cell, B1 – but still valid and still useful.

7-4

3 Putting information in a spreadsheet

Each cell in a spreadsheet can hold some bit of data. It might be a number, it might be your name, it might be a complex formula that keeps track of how much money you’ve spent on gasoline this year. In any case, you need to get that data into the cell before it’s going to do you any good.

3.1 Entering new data

Entering data in a spreadsheet isn’t like using a word processor. With a text editor, you just click the mouse where you want the cursor to appear, and then start typing things in at that point.

Putting stuff into spreadsheets is a little weird because although you click on the cell you want to put data into, you actually do your typing someplace else. That someplace else is called the editing box, and is usually located at the top of the screen.

Try this:

• Click on the cell A4. You’ll see that it gets a heavy border around it, indicating that this is the cell you’re working with.

• Start typing your name. Look towards the top of the screen (right about the column headers) – you’ll most likely see that whatever you type appear there.

• Press the Enter key. The data appears in the cell (or, at least as much of it as will fit at the moment).

3.2 Editing Data

Editing (changing) the contents of a spreadsheet is pretty much the same as entering new data. One option, in fact, is to just click on the cell you want to change and just start typing “over the top of” the existing data, wiping it out in the process.

A more genteel approach is to click once on the cell you want to edit, and then click up in the editing box and change whatever needs to be changed.

3.3 Deleting data

Fairly often, you’ll have data in a cell that you just want to get rid of. You should click on the cell, press the delete key, and press Enter. Don’t ever click on the cell and then press the space bar!!!. This looks as if it’s doing the right thing because the cell goes blank, but there is “hidden” data in there – that one space – and that’s enough to confuse some aspects of the program.

You can also delete the contents of a whole range of cells by selecting them with the mouse and choose Edit:Clear from the menus.

7-5

4 Things you can do to cells

4.1 Change the width of a column of cells

Somewhere in your spreadsheet, probably hidden up in the Format: menu somewhere, is an option to change the width of the currently selected column(s). Sometimes columns are too narrow to show all the data in each cell. Other times, a cell is wider than it needs to be, wasting valuable screen space. By selecting a column (or multiple columns) and then changing the width, you can optimize your screen usage and still be able to see all of your data.

Turns out, you can change the row height as well, although that’s not used as often.

Q. 3 How am I supposed to know how wide to make a cell?

(Answer on page 7-26.)

4.2 Change the appearance of the cell’s value

Many cells have something “in” them – they have a value. A spreadsheet gives you considerable control over what that value looks like.

Of course, most spreadsheets give you access to the usual word-processing-like formatting options: boldface, italics, centering – that sort of thing.

More useful are tools given you to change how a value is viewed. Take the number 0.25. This can show up on the screen as:

• 0.25

• 0.250000

• $0.25

• 25%

...depending on what meaning you need to ascribe to it. The number is still the same to the computer no matter how you make it look on the screen; changing the formatting is strictly for the benefit of humans trying to make sense out of the data.

It’s incredibly important to always make your data look the way it should. If you’re dealing with currency, make the cell look like currency! Same with percentages! It might not make any difference to the computer, but a human reader (including you, if you come back to a spreadsheet after a couple weeks) won’t be able to make heads nor tails out of your data if you don’t format it correctly.

Q. 4 Wait. I’m still not sure I get this whole formatting thing. How is it different than boldface?

7-6 (Answer on page 7-26.)

Q. 5 So, you mentioned something about this and dates before?

(Answer on page 7-26.)

Q. 6 Can I format lots of cells at once?

(Answer on page 7-26.)

5 Formulas

The real ball game in a spreadsheet is the use of formulas. This is where you can create those Magical Mystical connections between different cells and get the spreadsheet to do all the work for you.

5.1 Some quick examples

It’s a lot easier to play with formulas than it is to explain them, so we’ll do some quick examples.

The first thing you’ll notice is that they all start with a special formula character. Most modern spreadsheets use the equals sign (=); some older programs use the plus sign (+).

In either case,

• The first character of every formula must be the formula character

• The formula character has no effect on anything else. More specifically, it doesn’t act as an equals sign or a plus sign or anything else.

• The formula character appears exactly once; as the first character in the cell.

Q. 7 What’s with the formula character?

(Answer on page 7-26.)

For these examples, I’ve used the equals sign as the formula character. Note, too, that the actual names of the functions may be different in your spreadsheet; use the online help to track down the correct name.

7-7 =10 + 5 An incredibly simple formula, it does exactly what you think it would; it adds 10 and

5 and comes up with 15.

=A1 This forms a simple connection between the cell A1 and the cell that contains the formula. It says “Make sure I’m showing whatever cell A1 is showing”. As soon as you change the contents of cell A1, this cell will change immediately as well.

=A1 + 10 Pretty much the same as the last one, except this cell will always contain the value of A1 plus 10.

=A1 + B4 Again, the same, but this time we’re referring to two cells. Change either one of them and the cell containing the formula will automatically update itself.

=A1/20 A1 divided by 20

=A1 * B1 + C3 Multiplies A1 and B1, and then adds C3

=A1 * (B1 + C3) Here, we use parenthesis to change the normal order of operations. Add B1 and C3, and then multiply the result by A1.

Remember that arithmetic follows a set order of operations: stuff in parenthesis, followed by multiplication and division, followed by addition and subtraction.

=A1+A2+A3+A4+A5+A6 Add up all the values of cells A1 through A6. Wait, didn’t we have a better way to do this?

=sum(A1:A6) Aha! This uses a built-in function called “sum”. It takes one argument (a range of cells to add up). Be careful with the way it looks; formula character, followed by the name of the function, followed by the argument(s) in parenthesis. Again, remember that the actual name may be different for your particular spreadsheet.

=maximum(B1:C4) Find the biggest value in the range.

=minimum(B1:C4) Find the smallest value in the range

=maximum(B1:C4) - minimum(B1:C4) Find the difference between the biggest value and the smallest value in the range B1:C4. Note that the formula character appears once, at the beginning of the formula. Novices often want to put the formula character before every function name.

=average(A1:A10) As you’d expect, this averages the values in the given range.

Q. 8 How do I find out what built-in functions are available besides sum and average?

(Answer on page 7-26.)

7-8 5.2 Some things to notice about formulas

The formula character Don’t forget that the formula character appears exactly once, that it flags the cell as containing a formula, and that it has no effect on anything else.

Cell value vs. cell appearance This topic came up before when talking about dates; the text that appears in a cell isn’t necessarily the same as the actual underlying value. With formulas, the underlying value is the formula. What appears on the screen depends on the result of the formula and how you have the cell set up to display numbers and text.

Most of the time you will see the RESULTS of the formulas, not the formulas themselves. In the examples later on, we will show you the formulas so you can see what is happening. This diagram might help: Both sides are looking at EXACTLY the same spreadsheet. The only difference is that the results are shown in the left-hand example, and the formulas themselves are shown on the right.

Updates happen instantly If your formula references other cells, changing the value of a referenced cell immediately results in a change in the cell with the formula. There’s no waiting involved; things just go!

Order of operations counts! Don’t forget about order of operations; anything inside parenthesis happens first, followed by multiplication and division, followed by addition and subtraction. When in doubt, use parenthesis to say exactly what you mean. Better safe than sorry.

Empty cells and text cells have a value of zero Any cell that has nothing in it or has just a string in it has a numeric value of zero. Sometimes your formulas will seem to work but come up with the wrong data – check to make sure you aren’t referencing any empty cells. It can also be useful: if you don’t know how long a column is (because you keep adding data to it) you can feel comfortable using a formula like “=sum(A5:A10000)” and know it will come up with a good answer and not freak out.

6 Copying and Pasting formulas

Copy and Paste is magical in a spreadsheet when dealing with formulas.

Let’s look at the following snippet of a spreadsheet

7-9

A  B  C  D
1 Salespeople And Units Sold

2

3 Person  Cars Trucks Total
4 Bill 6 11 =B4+C4
5 Mike  11 19
6 Molly  6  13

The logic of the spreadsheet is very simple; we have two columns of numbers (Cars and Trucks) that we want to add together to get a total number of vehicles sold.

The formula in cell D4 is correct; it adds together the right data. Now let’s see what happens when we copy that cell and paste it into cell D5.

A  B  C  D
1 Salespeople And Units Sold

2

3 Person  Cars Trucks Total
4 Bill  6  11  =B4+C4
5 Mike  11 19  =B5+C5
6 Molly  6  13

6.1 What Happened?

Something odd, that’s for sure.

The cell references inside the formula changed when we copied the cell down a row. In fact, if you’ll notice, the formula changed so it will work on the new row the same way it worked on the old row.

Useful, that. What happens if we copy it down to the next row?

A  B  C  D
1 Salespeople And Units Sold

2

3 Person  Cars Trucks Total
4 Bill  6  11  =B4+C4
5 Mike  11 19  =B5+C5
6 Molly  6  13  =B6+C6

Excellent. Just what we wanted.

Just for kicks, even though it won’t help our spreadsheet, what happens if we move it over a column?

7-10

A  B  C  D  E
1 Salespeople And Units Sold

2

3 Person  Cars Trucks Total
4 Bill  6  11  =B4+C4 =C4+D4
5 Mike  11 19  =B5+C5
6 Molly  6  13  =B5+C5

Hmmmm. Well, that’s not good for much, but the pattern remains: we copied it over one column, and the cell references changed so all their columns went up by one.

6.2 What’s going on?

The formulas we’re dealing with use what we call relative addressing. That means that the formula really deals with spatial relationships instead of absolute relationships.

When we copy a formula, it preserves the spatial relationship (e.g., two cells over) and not the absolute relationship (e.g., cell B4).

Q. 9 What?????????

(Answer on page 7-26.)

Thus, in our example above, the formula in cell D4 really says “Take the value of the cell two over from me, and add it to the value of the cell one over from me”. When we copied around the formula, those relationships were preserved and the formulas were changed.

6.3 Why on earth does it work that way?

Spreadsheets are set up this way because 99.99% of all spreadsheets are a lot like the one in the short example above: Very repetitive data, all of which you want to handle in basically the same way.

Can you imagine if you had 200 salespeople and had to type in all of those formulas by hand? Forget it! – you might as well still be using paper and a calculator!

By using relative cell addressing, you can fill in those 199 extra cells simply and easily.

6.4 Steps for using formulas

Decide what you need the formula to do This sounds obvious, but it’s the first step in the STAIRS technique for a reason. Make sure you know what you’re up to; randomly typing in numbers won’t get you very far.

Write the formula once Write the formula in the first spot you need it.

7-11 Convince yourself it works Make sure it works. Try it out with weird data. Make sure you

fully understand what it’s doing and what data it’s working with.

Copy the living tar out of it Once you’ve written a formula that works, abuse it!

• Select the cell with the good formula

• Choose Edit:Copy to put it on the clipboard

• Use the mouse to select all the other cells where you need that formula

• Choose Edit:Paste

7 Absolute Addresses: No More Magic

Relative addressing with copy and paste is a wonderful tool. But there are times – not many times, granted, but times nonetheless – that we don’t want the formulas to change.

A  B  C
1 Salespeople And Sales

2

3  Commrate 8%

4

5 Person  Gross Sales Commission
6 Bill $135,000 =B6*C3
7 Mike  $110,000
8 Molly  $225,000

So far, so good. We can see that Bill’s commission is computed by multiplying the amount of sales (in cell B6) times the commission rate (up in cell C3). We’ve got a formula, it seems to work: let’s copy the tar out of it

A  B  C
1 Salespeople And Sales

2

3  Commrate 8%

4

5 Person  Gross Sales Commission
6 Bill  $135,000  =B6*C3
7 Mike  $110,000  =B7*C4
8 Molly  $225,000  =B8*C5

Well...not too good. The rows changed for the person (B6,B7,B8), but look at the commission rate reference! It changed too, and the bottom two formulas don’t do what we need them to do.

This is a case of the computer not being able to magically tell when we want the reference to change and when we want it to stay the same. We just need a way to tell it.

7-12 7.1 Absolute cell addressing

The way to “turn off the magic” is to use absolute cell addressing. By putting a dollar sign before the letter and number in an address (see the examples), you can tell the machine, “Hey! When I say A4, I mean A4!”.

By changing our original sheet to use the absolute address $C$3

A  B  C
1 Salespeople And Sales

2

3  Commrate 8%

4

5 Person  Gross Sales Commission
6 Bill $135,000 =B6*$C$3
7 Mike  $110,000
8 Molly  $225,000

We can copy the formula and see that the absolute address doesn’t change, while the relative address does.

A  B  C
1 Salespeople And Sales

2

3  Commrate 8%

4

5 Person  Gross Sales Commission
6 Bill  $135,000  =B6*$C$3
7 Mike  $110,000  =B7*$C$3
8 Molly  $225,000  =B8*$C$3

Q. 10 So, what good are absolute addresses, really?

(Answer on page 7-27.)

Q. 11 Why use a cell reference at all? Why not just have the formula be =B6*8%?

(Answer on page 7-27.)

Q. 12 In the spreadsheet above, which of the following formulas would act just like “=B6*$C$3”? ...and why?

7-13 • “=B6*C$3”

• “=B6*$C3”

• “=B6*C3”

(Answer on page 7-27.)

8 Naming Ranges

Quick! What does the formula “=sum(A5:A15) * $B$2” mean?

Of course, you have no idea. Sure, it sums up some numbers and then multiplies the result by something else, but we have no idea what it’s really up to. Even if you were looking at it in context (i.e., with the rest of the spreadsheet) it might be confusing.

OK. How about “=sum(car sales) * sales tax rate”?

Obviously, this one makes more sense. We’re computing the total sales tax on a group of automobile sales.

8.1 Why name a cell or cells?

The key to the increased readability is the use of named ranges. Once you’ve named a range, you can use the name in all of your formulas for increased readability.

At first glance, naming ranges seems like a waste of time. It isn’t. Why bother to name ranges?

• Names are just plain easier to understand than something like A1:A10.

• It’s really hard to read formulas when the cells they reference are off-screen. Larger spreadsheets, that take up more than one screen on the computer, are much harder to follow without names because you can’t always see the cells you’re referencing.

• While your spreadsheet might make perfect sense to you, in a business setting there’s no guarantee that you will be the one maintaining the spreadsheet. No matter what you do on a computer, always do your best to make sure someone else could figure it out.

8.2 Things to know about named ranges

Named ranges are always absolute references There’s no such thing as a named range that changed when used in a formula. This makes sense, if you think about it: you wouldn’t want a single name referring to more than one place on the spreadsheet, or you’d get incredibly confused.

Named ranges can be any size You can name as large or small a range as you like. In particular, you can name exactly one cell, which is a lot more useful than you might think.

7-14 You should almost always use a named range instead of an absolute cell address

Named ranges are absolute, and they’re so much easier to read that you’re almost always much better off using a named range.

9 Graphs and Charts

Most spreadsheet programs have features designed to help you create graphics from your data. These graphics can help you to illustrate the meaning of the numbers on the chart. They can also be used to stimulate interest, since graphs are often easier to read and understand than a bunch of numbers. Graphics can also be used to help make a point. A well - designed graph can do much to illustrate an idea. Graphs are commonly used in business and scientific settings to illuminate the meaning of spreadsheet data.

9.1 Parts of a graph

There will be a few key elements to any graph.

Data Range The graph is a graphical interpretation of some data. Generally, you will create a spreadsheet that generates some type of data, and use the graph to illustrate the data. When you define a graph, you will need some way to explain which data is being depicted. You can usually select the data you want with a range.

X and Y axes As you may remember, the X axis is the stuff that goes along the horizontal border of the chart. The Y axis is the vertical stuff. Most spreadsheet programs try to guess which stuff you want plotted as the X axis and which you want as the Y axis. If the graph looks completely wrong, you might want to look for some kind of feature that allows you to change the X - Y orientation.

Upper and Lower Bounds You might want to specify the upper and lower limits of the axes. The program will usually try to guess what you want, but you may still need to modify it.

Labels There will usually be an option for setting or changing the labels on a graph. This will allow you to put informative (or misleading, I guess) labels on the graph to make it easier to read. (unless you are sneaky.)

Graph type You will usually get some type of option to change the type of graph that is displayed. See below for more information about graph types.

9.2 Types of Graphs

There are a number of major styles you can choose from when creating a graph. The style you choose implies some things to your readers.

Area Charts Area charts show the relative contributions over time that each data series makes to a whole picture. For example, an area chart would be good to show how much the relative amounts of the principal and interest change over time of a mortgage.

7-15 Module #7: Spreadsheets Figure 1: An Area Chart

7-16 Module #7: Spreadsheets Figure 2: A Bar Chart

7-17 Bar Charts Bar charts compare distinct items or show single items at distinct intervals. Usually,

a bar chart is laid out with categories along the vertical axis and values along the horizontal axis. In other words, the bars are horizontally placed on the page. Bar charts are useful for comparing data items that are in competition, so it makes sense to place the longest bars on top and the others in descending order beneath the longest one.

Column Charts Column charts are like bar charts because they compare distinct items or show single items at distinct intervals. However, column charts have the categories arranged along the horizontal axis and the values along the vertical axis, so the bars are vertical on the chart.

A very common use for column charts is to display how values change over discrete units of time (monthly or yearly change, for example).

Line Charts a line chart plots the value of the variable as a specific point, then ’connects the dots’ in order to give you some idea of the relationship of consecutive points.

Line charts may also be used to show how the value of a variable changes over time. Unlike bar and column charts, line charts imply continuous change rather than a number of discrete points. For this reason, line charts are better at implying a trend. For example, if you are doing an experiment about the number of fish in a certain pond, you might be interested in the number of fish in the pond at a certain time, but you may also be very concerned with the trend of the fish population. Is it increasing or decreasing?

Just because a line chart implies trends does not necessarily mean they are there! Be careful when interpreting such charts that you do not automatically assume intermediate values by the line placement.

Pie Charts A pie chart is used to show proportions of a whole. It is very useful for figures that relate to a larger sum, such as demographic data or budget information. It is easy to get a feel for the relationship between component values when they are placed in a pie chart. Be careful that you do not have too many slices in the pie, or they will become meaningless.

Also, note that a pie chart is usually used as a snapshot of ONE moment in time. If you want to show relationships as part of a whole over time, you would use an area chart. If you want to look at a number of pie charts at once, you might consider a doughnut chart. (look it up in online help or just play around with it!)

Scatter Plot A scatter plot is the simplest type of graph. It simply plots the data points against their values, without adding an connecting lines, bars or other stuff. This is visually the least appealing type of graph, but the lack of bells and whistles can actually be an advantage. Since all the other types of graphs tend to have some kind of psychological implication built in (eg bar charts imply comparison, line graphs imply continuity), scatter diagrams are devoid of this type of clutter. If you are searching for the patterns and meaning in a graph, you may find the scatter diagram the clearest representation of the data. Once you understand what it means, you can use one of the other types of graph to give your readers whichever impression you choose.

3-D Charts Many of the above charts can be created in 3-dimensional forms. The charts work pretty much the same way, but they can be a little more complex to work with. A three

7-18 Module #7: Spreadsheets Figure 3: A Column Chart

7-19

Figure 4: A Line Chart

dimensional chart can be rotated so that it can be seen from other perspectives. 3-D charts are often used for the added dramatic impact they provide to a presentation.

9.3 Misleading with Graphs

Graphs can send a very powerful message to people. The use of images makes a much more vivid impact that straight numbers. Graphs also have the capability to strengthen implications about data based on the type of graph, colors used, and other tools. Just because you see a graph does not mean you should believe it. Examine carefully where the data came from, and what it is telling you.

It is possible to make exactly the same data appear to have completely different meanings. Examine Figure 8 (on page 28) for an example.

If you look carefully, you will note that the graphs are both showing exactly the same thing, but by careful manipulation of the graph sizes, axis scales, and titles, the two charts appear to have exactly OPPOSITE meanings.

7-20

Figure 5: A Pie Chart

10 Laboratory Assignment

1. Use a tutorial program to examine a spreadsheet application. Use the help tutorial built into your application or one provided by your instructor.

2. Create a small spreadsheet that solves the following problems:

• Open your spreadsheet software

• Put your name in cell C1

• Use a formula to calculate the value of this problem: 3458/1729+4 Put a label next to the cell so you know what it means. DO NOT just write down the answer! Have the formula figure it out!

• Place the day of the month you were born in a cell underneath the formula. Label this cell as well.

• Estimate the number of cans of soup you have in your pantry right now. Place that value under the day of the month, and label it.

7-21

Figure 6: A Scatter Plot

• Figure out the total ages of the pets in your family. If you want to use a different cell for each pet, that is fine, but label them clearly.

• Estimate the amount of time it took you to get to school this morning in minutes. Label this value.

• Move to another part of the page and figure the sum of all the above values.

• Write a formula that will multiply together all the values

• Write a formula that will show the average of all values.

• It’s a little known fact that your lucky number can be derived from these values! The formula is this: Take the Birth date, multiply it by the cans of soup squared. Divide this entire quantity by the sum of the age of your pets and time to school. The resulting value is your lucky number. Figure it out and put it on the spreadsheet. Use CELL REFERENCES ONLY! Some of the values might change, and you want to be able to re-figure your lucky number.

• Name the range which contains the first formula (3458...), the birth date, cans of soup, age of pets, and time to school. Call this range something clever like Values. You might

7-22

Figure 7: A 3D Chart

need to look for some kind of range naming command.

• Calculate the average value of the range. Use the range name in the average formula.

• create some kind of chart demonstrating the values in your range. Look for some type of chart-creation tool.

• Save and print your spreadsheet.

One Example:

3. Create a spreadsheet to help you track your grades for this class. The actual details may vary, but here are some guidelines you can use if you don’t get other instructions from your teacher. Get a list of your current grades from your teacher. Assume that all lab grades together will be worth 40% of the final score. Assume that the midterm will be worth 20 percent of the final score, and that the final exam will be worth another 20 percent.

7-23 Finally, consider your final project the last 20 %.

NOTE: This is NOT necessarily the grading scheme your class will use. Check with your teacher so you know exactly how your grade will be calculated.

Write a spreadsheet that allows you to place values in cells and automatically calculates what your final score will be. You should be able to use this with your actual scores for “what if” analysis. EG “If I get 80% on the final, what do I need to get on my final project for an A?”.

Print and save this spreadsheet.

11 Vocabulary

Spreadsheet a class of application software that focuses on defining numerical relationships and encourages “what if” analysis.

“What if” analysis The process of playing with numbers to see how they interact in a spreadsheet.

cell The basic unit of memory in a spreadsheet. One cell will contain text, a number, or a formula.

cell address Usually the intersection of a cell’s row and column.

row The horizontal units in a spreadsheet. Think “Row ACROSS a river.” Rows go across.

column The vertical units in a spreadsheet. Think of the Greek columns. They would look pretty silly going sideways. Columns go up and down.

edit box The part of the spreadsheet that allows you to edit the value of a cell. Usually near the top of the spreadsheet.

text Characters or numbers that are interpreted in ASCII or some similar format. The spreadsheet cannot do math on characters.

numbers Values which can be calculated. Sometimes numbers look like text, but they are really numbers. (See cell formatting)

formulas A formula is a mathematical expression that refers to cells in other parts of the spreadsheet.

range A group of cells usually denoted by two corner cells seperated by a colon. (A6:F8) describes a rectangle with one corner at A6 and another corner at F8.

cell formatting A special kind of formatting that describes how data will be represented. Changes the appearance of the cell, but not its value.

formula character A special character (usually the = or + sign) that informs the spreadsheet that the contents of the cell will be a formula.

7-24 order of operations A convention in mathematics describing the order that certain mathemat-

ical operations will occur. If in doubt, use plenty of parentheses to make your formula completely clear. Standard order of operations: (),ˆ ,*/,+-.

relative addressing The standard form of reference to a cell. When you use a relative reference and copy the formula, the new formula preserves the relationship between itself and any referred cells.

absolute addressing Referring to a cell with one or more dollar signs in the cell address. When copied, the locked parts of the address stay the same.

replication A term sometimes used to refer to the special copy and paste behavior of spreadsheets.

named ranges ranges of one or more cells that have been given a name for ease of handling. A named range is usually much easier to work with and understand than the built-in cell addresses.

12 Summary

Spreadsheets are an incredibly powerful class of application programs that allow the user to specify and manipulate relationships between numbers. Spreadsheet applications are especially useful for “what if” analysis of complex problems. Spreadsheets also make the graphical depiction and analysis of data much easier through graphing tools.

7-25

Answers to Questions

1. Dates are represented by numbers (usually, in fact, as the number of days since January 1, 1900) for a good reason. If we want a date to increase by one week, it’s a lot easier to just add 7 to it than to try and figure out if the month ended or if we’re in a new year or whatever. Adding seven is easy. Keeping a calendar in your head is hard.

Of course, most of the time we don’t see the number; we see something like ”Jan 11, 1984”. It’s useful to know that there’s a number hiding underneath, though, so you can figure out what’s going on when things get messed up.

2. Absolutely! How smart of you to notice!! When we discussed the essential things a computer can deal with, we talked about numbers, text (often stored in ASCII form) and commands. Spreadsheets are a special way of organizing the computer’s memory to handle these basic elements we already know.

3. You’re not. You can, of course, guess, but most spreadsheets have an ”AutoFit” feature that automatically finds the widest cell in a column and changes the column so it fits.

4. Textual attributes, like boldface and italics and the color red, change the appearance of whatever shows up in the cell. Changing the cell formatting actually changes what is going to show up in the cell.

5. Not really, but good question anyway. Here’s a useful experiment:

• Enter the number 36000 into a cell

• Change the formatting of the cell to be a percentage

• Change the formatting of the cell to be of type date

Remember, the underlying data (the number 36000) never changes; only what we see on the screen changes.

6. Don’t ask, just try it! The machine doesn’t bite, and you can always use the Edit:Undo command.

The quick answer, for those of you without a machine in front of you, is ”yes”.

7. We need a formula character at the beginning of every formula, first and foremost, so the spreadsheet knows it’s a formula and not just normal text.

8. Your first step should always be to check the online help. Try a search on the word ”function”.

9. Good question. Think of it this way:

If someone asks you how to get to Hal O’Gen’s house, you could say ”Go to 1215 West Seventh Street” (absolute directions), or you could say ”Go two blocks up and three blocks over” (directions relative to where you’re standing right now).

By default, spreadsheets use the relative directions. When you copy the formula around, it is changed in whatever way is necessary to preserve the relative relationships.

7-26 10. I’ve tended to use absolute addressing mostly for ”rates”. Tax rates, interest rates, commis-

sion rates – all are things that you probably want to apply to a large set of numbers.

11. NOOOOOOOOOO! If you do that, you lose the ability to ask some very important ”What if...?” questions – like, ”How much money can the company save if we lower the commission rate to 7.5%?”. If you keep the rate in exactly one place, you can easily change it and then watch those changes propagate through the rest of the spreadsheet. If you put it in the formula, it’s hard to change and/or track down.

12. “=B6*C$3” is the best answer for this particular spreadsheet.

Notice when we were copying stuff DOWN, the C part of “C3” never caused us any trouble. Since we were copying the formula from column C to Column C (from C to shining C?), the reference to C didn’t absolutely HAVE to be locked. The 3 part was the thing causing us trouble, so we had to lock it up. Every great once in a while you might come across a situation where you have to lock just part of a cell reference like this. Don’t worry about it. Just remember that it is possible so you can try it someday when nothing else works.

7-27

Module #7: Spreadsheets Figure 8: A Misleading Graph

7-28 Module #7: Spreadsheets Figure 9: Sample Spreadsheet

7-29 Module #7: Spreadsheets

7-30