\documentstyle[twoside,12pt]{article}
\usepackage{htx}
\me{Bill Dueber}	% author(s) names and/or email address
%\psdraft
\begin{document}

% Page 1

\docheader{Introduction to Computing}
{7}   % module number
{Spreadsheets }  % module name

\begin{goallist}
  \item Enter and edit data in cells of a spreadsheet.
  \item Use simple formulas
  \item Cut/copy/paste data and formulas
  \item Give names to ranges of cells, and use those range names in formulas
  \item Create a spreadsheet to represent numeric dependencies for simple
problems
  \item Use a spreadsheet to answer ``what if'' questions about simple problems.
  \item Create a simple graph from a number of data points

\end{goallist}

\begin{prereqs}

  \item Familiarity with normal file and editing operations (opening/saving
files,
    using the mouse, etc.)
  \item Some familiarity with word-processing concepts

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

\end{prereqs}

\section{Discussion}

\subsection[s]{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 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 {\em can} copy another program's ``look and feel''
(within reason) without getting sued.

\subsection[s]{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:

\begin{itemize}
    \item Interest earned is 4\% of the current balance per year
    \item The final car price is the dealer price + cost for extras + 10\%
commission
    \item My checkbook balance is the amount of the initial deposit + all
the deposits since then - all the withdrawals since then
\end{itemize}

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

\subsection[s]{``What if...'' questions}

Spreadsheets are most powerful when we're using them to answer ``what if''
questions. Since we're setting up {\em 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:

\begin{itemize}
    \item The Fed is hinting that they'll raise interest rates 0.5\%. What
will that do to our investments?

    \item How many more fish will survive next year if we cut toxic
dumping in the lake by 5\%?

    \item 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?

\end{itemize}

\subsection[s]{Problems that lend themselves to spreadsheets}

\begin{itemize}
  \item Any problem that involve a lot of numbers, many of which are
dependent on each
    other or are likely to change.
  \item Anything you might need to graph, since all spreadsheets have a
graphing program built into them.
  \item Any problem where you need to play with numbers in order to find an
``optimal'' solution.

\end{itemize}

\subsection[s]{Problems that spreadsheets aren't good at solving}

\begin{itemize}

  \item Anything involving a lot of text. Spreadsheets don't do text well.

  \item 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.
 
 \item 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.

\end{itemize}

\section{Spreadsheet Concepts}

\subsection[s]{Cells}

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

Each cell refers to a specific spot in memory.

Each cell also has a unique {\em 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.

\begin{center}
\begin{tabular}{|l|l|l|l|l|}
\hline
 &   A &      B &       C &      D \\
\hline
1 &   A1 &      B1 &      C1 &      D1 \\
\hline
2 &   A2 &      B2 &      C2 &      D2 \\
\hline
3 &   A3 &      B3 &      C3 &      D3 \\
\hline
4 &   A4 &      B4 &      C4 &      D4 \\
\hline
\end{tabular}
\end{center}

Each cell has a particular {\em 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.

\begin{definition}
\item[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.
{\em All text has a numeric value of zero!}

\item[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 (%), even
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.


\begin{quest} Why pretend dates are numbers? Why not just use text?
\end{quest}

\item[Formulas]
A {\em 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 {\em lot} about formulas in a
little bit.

\end{definition}

\begin{quest}
Hmmm....  text, numbers, and commands (formulas).  Haven't we seen all
this before?
\end{quest}


\subsection[s]{Ranges}

Any rectangular group of cells is called a {\em range}. Ranges are useful
in various formulas that can work on a large number of cells at a time
(e.g., the {\em 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).

\begin{definition}
\item[A1:A10] The first ten cells in column A
\item[A1:D1] The first four cells in row 1
\item[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).
\item[B1:B1] A very small range -- just the one cell, B1 --  but still
valid and still useful.
\end{definition}

\section{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.

\subsection[s]{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 {\em editing
box}, and is usually located at the top of the screen.

Try this:

\begin{itemize}
\item 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.
\item 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.
\item Press the Enter key. The data appears in the cell (or, at least as
much of it as will fit at the moment).
\end{itemize}

\subsection[s]{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.

\subsection[s]{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.
{\em Don't ever click on the cell and then press the space bar!!!}. This
{\em 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.

\section{Things you can do to cells}

\subsection[s]{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.

\begin{quest}
How am I supposed to know how wide to make a cell?
\end{quest}


\subsection[s]{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 {\em how a value is viewed}.
Take the number 0.25. This can show up on the screen as:
\begin{itemize}
\item 0.25
\item 0.250000
\item \$0.25
\item 25\%
\end{itemize}

...depending on what meaning you need to ascribe to it. The number is
{\em 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.

{\em 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 {\em 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.

\begin{quest}Wait. I'm still not sure I get this whole formatting thing.
How is it different than boldface?
\end{quest}

\begin{quest}So, you mentioned something about this and dates before?
\end{quest}

\begin{quest} Can I format lots of cells at once?
\end{quest}


\section{Formulas}

The real ball game in a spreadsheet is the use of {\em 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.


\subsection[s]{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
{\em formula character}. Most modern spreadsheets use the equals sign (=);
some older programs use the plus sign (+).

In either case,
\begin{itemize}
\item The first character of every formula must be the formula character
\item The formula character has {\em no effect} on anything else. More
specifically, it doesn't act as an equals sign or a plus sign or anything
else.
\item The formula character appears {\em exactly} once; as the first
character in the cell.
\end{itemize}

\begin{quest}What's with the formula character?
\end{quest}


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.

\begin{description}
\item[=10 + 5] An incredibly simple formula, it does exactly what you think
it would; it adds 10 and 5 and comes up with 15.
\item[=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.
\item[=A1 + 10] Pretty much the same as the last one, except this cell will
always contain the value of A1 plus 10.
\item[=A1 + B4] Again, the same, but this time we're referring to
{\em two} cells. Change either one of them and the cell containing the
formula will automatically update itself.
\item[=A1/20] A1 divided by 20
\item[=A1 * B1 + C3] Multiplies A1 and B1, and then adds C3
\item[=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 {\em order of operations}: stuff in
parenthesis, followed by multiplication and division, followed by addition
and subtraction.

\item[=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?
\item[=sum(A1:A6)] Aha! This uses a built-in {\em function} called ``sum''.
It takes one {\em 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.
\item[=maximum(B1:C4)] Find the biggest value in the range.
\item[=minimum(B1:C4)] Find the smallest value in the range
\item[=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 {\em once}, at the beginning of the formula.
Novices often want to put the formula character before every function name.
\item[=average(A1:A10)] As you'd expect, this averages the values in the
given range.
\end{description}

\begin{quest} How do I find out what built-in functions are available
besides sum and average?
\end{quest}

\subsection[s]{Some things to notice about formulas}

\begin{description}
\item[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.
\item[Cell {\em value} vs. cell {\em 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,
{\em 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.

\smallskip
\begin{center}
\mbox{\psfig{file=showfrm.ps,width=6.5in}}
\end{center}

\item[Updates happen {\em instantly}] If your formula references other
cells, changing the value of a referenced cell {\em immediately} results
in a change in the cell with the formula. There's no waiting involved;
things just {\em go}!

\item[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.

\item[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.

\end{description}


\section{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

\begin{center}
\begin{tabular}{|l|l|l|l|l|}
\hline
 &   A &        B &       C &           D \\
\hline
1 &   Salespeople And Units Sold \\
\hline
2 & & & & \\
\hline
3 &  Person &  Cars &    Trucks &     Total \\
\hline
4 &   Bill &    6 &       11 &          =B4+C4 \\
\hline
5 &   Mike &    11 &      19 &           \\
\hline
6 &   Molly &   6   &    13 &    \\
\hline
\end{tabular}
\end{center}

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.

\begin{center}
\begin{tabular}{|l|l|l|l|l|}
\hline
 &   A &        B &       C &           D \\
\hline
1 &   Salespeople And Units Sold \\
\hline
2 & & & & \\
\hline
3 &  Person &  Cars &    Trucks &     Total \\
\hline
4 &   Bill &    6 &       11 &          =B4+C4 \\
\hline
5 &   Mike &    11 &      19 &          =B5+C5 \\
\hline
6 &   Molly &   6 &      13 & \\
\hline
\end{tabular}
\end{center}

\subsection[s]{What Happened?}

Something odd, that's for sure.

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

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

\begin{center}
\begin{tabular}{|l|l|l|l|l|}
\hline
 &    A &       B &       C &           D  \\
\hline
1 &  Salespeople And Units Sold \\
\hline
2 & & & & \\
\hline
3 &   Person &  Cars &    Trucks &     Total \\
\hline
4 &   Bill &    6 &       11 &          =B4+C4 \\
\hline
5 &   Mike &    11 &      19 &          =B5+C5 \\
\hline
6 &   Molly &   6 &       13 &          =B6+C6 \\
\hline
\end{tabular}
\end{center}

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?

\begin{center}
\begin{tabular}{|l|l|l|l|l|l|}
\hline
 &    A &       B &       C &           D &           E \\
\hline
1 &   Salespeople And Units Sold \\
\hline
2 & & & & & \\
\hline
3 &   Person &  Cars &    Trucks &     Total \\
\hline
4 &   Bill &    6 &       11 &          =B4+C4 &     =C4+D4 \\
\hline
5 &   Mike &    11 &      19 &          =B5+C5 & \\
\hline
6 &   Molly &   6 &       13 &          =B5+C5 & \\
\hline
\end{tabular}
\end{center}

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.

\subsection[s]{What's going on?}

The formulas we're dealing with use what we call {\em relative
addressing}. That means that the formula really deals with {\em 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).

\begin{quest}What?????????
\end{quest}

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.

\subsection[s]{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.

\subsection[s]{Steps for using formulas}

\begin{definition}
\item[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.
\item[Write the formula {\em once}] Write the formula in the first spot
you need it.
\item[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.
\item[Copy the living tar out of it] Once you've written a formula that
works, abuse it!
\begin{itemize}
\item Select the cell with the good formula
\item Choose Edit:Copy to put it on the clipboard
\item Use the mouse to select all the other cells where you need that formula
\item Choose Edit:Paste
\end{itemize}
\end{definition}


\section{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
{\em don't} want the formulas to change.

\begin{center}
\begin{tabular}{|l|l|l|l|}
\hline
 &    A &       B &               C \\
\hline
1 &   Salespeople And Sales \\
\hline
2 & & & \\
\hline
3 & &          Commrate  &      8\% \\
\hline
4 & & & \\
\hline
5 &   Person &  Gross Sales &     Commission \\
\hline
6 &  Bill &   \$135,000 &       =B6*C3 \\
\hline
7 &   Mike &    \$110,000 & \\
\hline
8 &   Molly &   \$225,000 & \\
\hline
\end{tabular}
\end{center}

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

\begin{center}
\begin{tabular}{|l|l|l|l|}
\hline
 &    A &       B &               C \\
\hline
1 &   Salespeople And Sales \\
\hline
2 & & & \\
\hline
3 & &        Commrate &        8\% \\
\hline
4 & & & \\
\hline
5 &  Person &  Gross Sales &     Commission \\
\hline
6 &   Bill &    \$135,000 &        =B6*C3 \\
\hline
7 &  Mike  &  \$110,000 &       =B7*C4 \\
\hline
8 &  Molly &  \$225,000 &       =B8*C5 \\
\hline
\end{tabular}
\end{center}

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.

\subsection[s]{Absolute cell addressing}

The way to ``turn off the magic'' is to use {\em 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 {\em mean}
A4!''.

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

\begin{center}
\begin{tabular}{|l|l|l|l|}
\hline
 &    A &       B &               C \\
\hline
1 &   Salespeople And Sales \\
\hline
2 & & & \\
\hline
3 & &        Commrate &        8\% \\
\hline
4 & & & \\
\hline
5 &  Person &  Gross Sales &     Commission \\
\hline
6 &   Bill &    \$135,000 &        =B6*\$C\$3 \\
\hline
7 &  Mike  &  \$110,000 &        \\
\hline
8 &  Molly &  \$225,000 &        \\
\hline
\end{tabular}
\end{center}

We can copy the formula and see that the absolute address doesn't change,
while the relative address {\em does}.

\begin{center}
\begin{tabular}{|l|l|l|l|}
\hline
 &    A &       B &               C \\
\hline
1 &   Salespeople And Sales \\
\hline
2 & & & \\
\hline
3 & &        Commrate &        8\% \\
\hline
4 & & & \\
\hline
5 &  Person &  Gross Sales &     Commission \\
\hline
6 &   Bill &    \$135,000 &        =B6*\$C\$3 \\
\hline
7 &  Mike  &  \$110,000 &       =B7*\$C\$3 \\
\hline
8 &  Molly &  \$225,000 &       =B8*\$C\$3 \\
\hline
\end{tabular}
\end{center}

\begin{quest} So, what good are absolute addresses, really?
\end{quest}

\begin{quest}Why use a cell reference at all? Why not just have the formula be
=B6*8\%?
\end{quest}

\begin{quest}
In the spreadsheet above, which of the following formulas would act
just like ``=B6*\$C\$3''?   ...and why?

\begin{itemize}
\item ``=B6*C\$3''
\item ``=B6*\$C3''
\item ``=B6*C3''
\end{itemize}

\end{quest}


\section{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.

\subsection[s]{Why name a cell or cells?}

The key to the increased readability is the use of {\em 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?

\begin{itemize}
\item Names are just plain easier to understand than something like A1:A10.
\item It's {\em 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.
\item While your spreadsheet might make perfect sense to {\em you}, in a
business setting there's no guarantee that {\em 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.
\end{itemize}

\subsection[s]{Things to know about named ranges}

\begin{description}
\item[Named ranges are always {\em 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.

\item[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.

\item[You should almost always use a named range instead of an absolute
cell address]\hfill\break
Named ranges {\em are} absolute, and they're so much easier
to read that you're almost always much better off using a named range.
\end{description}


\section {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.

\subsection[s] {Parts of a graph}

There will be a few key elements to any graph.  
\begin{definition}
\item [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.

\item [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.

\item [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.

\item [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.)

\item [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.

\end{definition}

\begin{figure}
\begin{center}
\mbox{\psfig{file=area.ps}}
\caption{An Area Chart}
\end{center}
\end{figure}

\begin{figure}
\begin{center}
\mbox{\psfig{file=barcht.ps}}
\end{center}
\caption{A Bar Chart}
\end{figure}

\subsection[s] {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.

\begin {definition}
\item [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.

\item [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.

\begin{figure}
\begin{center}
\mbox{\psfig{file=colcht.ps}}
\end{center}
\caption{A Column Chart}
\end{figure}

\item[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).

\item[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 {\em trend}
of the fish population.  Is it increasing or decreasing?  

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=linecht.ps}}
\end{center}
\caption{A Line Chart}
\end{figure}

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.

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=piecht.ps,width=6.5in}}
\end{center}
\caption{A Pie Chart}
\end{figure}

\item [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!)

\item [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.

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=scatter.ps}}
\end{center}
\caption{A Scatter Plot}
\end{figure}

\item [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 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.

\end {definition}

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=3Dcht.ps}}
\end{center}
\caption{A 3D Chart}
\end{figure}

\subsection[s]{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~\ref{mislead} (on page
\pageref{mislead}) 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.

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=mislead.ps,width=6.5in}}
\end{center}
\caption{A Misleading Graph}
\label{mislead}
\end{figure}

\section{Laboratory Assignment}  % comment out if not applicable

\begin {enumerate}

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

\item Create a small spreadsheet that solves the following problems:

\begin{itemize}

\item Open your spreadsheet software

\item Put your name in cell C1

\item 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!

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

\item 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.

\item 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.

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

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

\item Write a formula that will multiply together all the values

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

\item 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.

\item 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 need to look for some
kind of range naming command.  

\item Calculate the average value of the range.  Use the range name in
the average formula.

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

\item Save and print your spreadsheet.

\end{itemize}

One Example:

\begin{figure}[t]
\begin{center}
\mbox{\psfig{file=sampless.ps,width=6.5in}}
\end{center}
\caption{Sample Spreadsheet}
\end{figure}

\item 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.

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.


\end {enumerate}



\section{Vocabulary}
\begin{vocab}	% vocabulary/main concepts list
\item [\bf Spreadsheet] a class of application software that focuses
on defining numerical relationships and encourages ``what if''
analysis.

\item[\bf ``What if'' analysis] The process of playing with numbers to
see how they interact in a spreadsheet.

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

\item[\bf cell address] Usually the intersection of a cell's row and
column.

\item[\bf row] The horizontal units in a spreadsheet.  Think ``Row
ACROSS a river.''  Rows go across.

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

\item[\bf edit box] The part of the spreadsheet that allows you to
edit the value of a cell.  Usually near the top of the spreadsheet.

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

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

\item[\bf formulas] A formula is a mathematical expression that refers
to cells in other parts of the spreadsheet.

\item[\bf 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.

\item[\bf cell formatting] A special kind of formatting that describes
how data will be represented.  Changes the {\em appearance} of the
cell, but not its {\em value.}

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

\item[\bf order of operations] A convention in mathematics describing
the order that certain mathematical operations will occur.  If in
doubt, use plenty of parentheses to make your formula completely
clear.  Standard order of operations:  {\mycatcodes (),^,*/,+-}.

\item[\bf 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 {\em relationship} between itself and any
referred cells.

\item[\bf 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.

\item[\bf replication] A term sometimes used to refer to the special
copy and paste behavior of spreadsheets.  

\item[\bf 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.

\end{vocab}

\section{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.  

\anslist{abssans}
\end{document}

















