Module #6: Spreadsheets \documentstyle{fmmccx} \me{Bill Dueber} % author(s) names and/or email address \seticondir{../icons/} \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{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{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{`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{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{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{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{tabular}{|l|l|l|1|l|} & A & B & C & D \\ 1 & A1 & B1 & C1 & D1 \\ 2 & A2 & B2 & C2 & D2 \\ 3 & A3 & B3 & C3 & D3 \\ 4 & A4 & B4 & C4 & D4 \\ \end{tabular} 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? \ans 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 {\em 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. \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? \ans 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. \end{quest} \subsection{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{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{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{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{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? \ans 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. \end{quest} \subsection{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? \ans Textual attributes, like boldface and italics and the color red, change the appearance of {\em whatever shows up in the cell}. Changing the cell formatting actually changes {\em what is going to show up in the cell}. \end{quest} \begin{quest}So, you mentioned something about this and dates before? \ans Not really, but good question anyway. Here's a useful experiment: \begin{itemize} \item Enter the number 36000 into a cell \item Change the formatting of the cell to be a percentage \item Change the formatting of the cell to be of type date \end{itemize} Remember, the underlying data (the number 36000) never changes; only what we see on the screen changes. \end{quest} \begin{quest} Can I format lots of cells at once? \ans 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". \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{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? \ans 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. \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? \ans Your first step should {\em always} be to check the online help. Try a search on the word "function". \end{quest} \subsection{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. \begin{rightimage}{\icondir showfrm.gif} \end{rightimage} \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 {\em NOTE:}Need a table \begin{tabular}{|l|l|l|1|l|} & 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 & \\ \end{tabular} 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{tabular}{|l|l|l|1|l|} & 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 & \\ \end{tabular} \subsection{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{tabular}{|l|l|l|1|l|} & 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 \\ \end{tabular} 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{tabular}{|l|l|l|1|l|l|} & 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 & \\ \end{tabular} 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{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????????? \ans 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. \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{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{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{tabular}{|l|l|1|l|} & 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 & \\ \end{tabular} 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{tabular}{|l|l|l|1|} & 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 \\ \end{tabular} 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{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{tabular}{|l|l|l|1|} & 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 & \\ \end{tabular} We can copy the formula and see that the absolute address doesn't change, while the relative address {\em does}. \begin{tabular}{|l|l|l|1|} & 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 \\ \end{tabular} \begin{quest} So, what good are absolute addresses, really? \ans I've tended to use absolute addressing mostly for "rates". Tax rates, interest rates, commission rates -- all are things that you probably want to apply to a large set of numbers. \end{quest} \begin{quest}Why use a cell reference at all? Why not just have the formula be =B6*8\%? \ans 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. \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} \ans ``=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 {\em 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. \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{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{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] 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 {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} \subsection {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] \begin{rightimage}{\icondir area.gif} 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. \end{rightimage} \item [Bar Charts] \begin{rightimage}{\icondir barcht.gif} 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. \end{rightimage} \item [Column Charts] \begin{rightimage}{\icondir colcht.gif} 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). \end{rightimage} \item[Line Charts] \begin{rightimage}{\icondir linecht.gif} 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? 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. \end{rightimage} \item [Pie Charts] \begin{rightimage}{\icondir piecht.gif} 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!) \end{rightimage} \item [Scatter Plot] \begin{rightimage}{\icondir scatter.gif} 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. \end{rightimage} \item [3-D Charts] \begin{rightimage}{\icondir 3Dcht.gif} 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{rightimage} \end {definition} \subsection{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 the figures below for an example: \begin{leftimage}{\icondir mislead.gif} \end{leftimage} 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. \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{leftimage}{\icondir sampless.gif} \end{leftimage} \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} \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[\bf 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: (),^,*/,+-. \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. \end{document}