|
|
|
|
Though spreadsheets have been used for hundreds of years by accountants, only recently has the use of computerized spreadsheets been possible. It wasn't until 1978 that a computer program called VisiCalc (short for visual calculator) written by Harvard Business School student, Daniel Bricklin emerged. VisiCalc was the first "killer" application for personal computers. In fact, much of the early growth of the personal computer industry should be accredited to the spreadsheet. The main power of the spreadsheet is how dynamic it is. That is, it gives the ability to the user to easily change input data and allow the spreadsheet to automatically change any output data. Other useful purposes of the spreadsheet are the ability to allow the user to easily discover patterns in numerical data, to graphically display data, and to perform mathematical and logical operations with ease. Today's world is one in which spreadsheets are used in nearly every business. However, the purpose of this text is not just to provide students with a introduction to the use of spreadsheets. The purpose of this text is instead to use the modern spreadsheet as a tool for solving problems. It is intended to develop a student's ability to generate algorithms. The spreadsheet can also be used to avoid messy arithmetic which may be involved in the search for patterns in data and/or in solving real-world problems. It is the belief of the author that implementing an algorithm on a spreadsheet builds a student's critical thinking skills. For example, while fancy computer programs will always exist to apply various apportionment methods, the "behind the scenes" look at spreadsheet implementation is invaluable. Furthermore, the use of the spreadsheet to "discover" mathematical ideas is ripe with possibilities. One aim of this text is to exploit those possiblities. The text is to be used as a companion to the Excursions in Modern Mathematics textbook written by Peter Tannenbaum and Robert Arnold. While some of the techniques of implementing algorithms are discussed, many others are left to the student as exercises. Thus, the exercises serve as the major component to this text. In this author's opinion, part of the beauty of the Excursions text is the fact that it is not driven by formulas and equations to be memorized by the reader. The student is encouraged, rather, to develop their own problem solving strategies. One goal of this supplement is to extend this approach. Note to the instructor: Some chapters in this text are far more difficult than others. For example, chapter 1 may be the most difficult chapter of the entire text. With the exception of the introductory chapter 0, the chapters are essentially independent of each other and an instructor may choose to use some or all of the chapters in his/her course. It should also be noted that the syntax used in the text is for Microsoft Excel 2000 and instructors who choose to use a different spreadsheet package will need to alert students accordingly. Excel files used in the development of this text can be downloaded from http://www.stcloudstate.edu/~dbuske/Excel/ Note to the student: This text serves as more of a guidebook for an excursion into the mathematics of spreadsheets than as a technical manual. In order to truly master the ideas in this text, thre reader needs to do the exercises. Mathematics is not a spectator sport!
Spreadsheets are a rectangular grid of dynamic cells indexed by a column letter and a row number. We identify a particular cell in the second column and third row of the grid by using the notation B3. The spreadsheet in Figure 1 shows the conversion of several different lengths given in feet to meters.
Figure 1: Converting feet to meters with a spreadsheet.The key behind effectively using spreadsheets is the use of formulas. Formulas allow a user to change input in one or more cells and have the spreadsheet automatically update output. Since 1 foot is approximately 0.3048 meters, we use the formula =A3*0.3048 in cell B3. If one wanted to repeat this process of conversion on the remaining three measurements given in feet, one would simply need to copy the formula in cell B3 to the cells B4 through B6 below it. Notice that the formula changes when copied. When the formula from cell B3 is copied into cell B4, it becomes the formula =A4*0.3048. This is because the reference to cell A3 is a relative cell reference. If the user had used the absolute cell reference =$A$3*0.3048 when entering the formula in cell B3, the resulting number in cell B3 would be the same. However, if this formula would have been copied to cells B4 through B6 below, then all of the cells in column B would have given the same output 0.6096. The idea behind a relative cell reference is that by using the formula =A3*0.3048, the computer is taking the value in the cell to its left, multiplying that value by 0.3048 and putting the resulting number in cell B3. Relative to the location of B3, the formula is getting its input from the cell that is in the same row but one cell to the left. This explains what happens when this formula is copied down column B to produce the output of Figure 1. On the other hand, if an absolute cell reference were used in cell B3, say =$A$3*0.3048, then the formula is getting its input from cell A3 period. That is, regardless of where this formula is copied, the input will continue to come from cell A3. What would happen if another type of absolute cell reference were used in cell B3, say =$A3*0.3048? The output to cell B3 would remain the same as before; however, when the formula is copied down to cells B4 through B6 below, the new formulas are slightly different. The 'correct' output is still given, but cell B4 now contains the formula =$A4*0.3048. If this formula were copied over to cell C4, it would produce the same output as what is in cell B4. Note that this would not happen if a purely relative cell reference were used. The idea is that the formula is getting its input from the same row and from column A, not from the column to the immeadiate left. To further illustrate this idea, consider what happens when the formula =A\$3*0.3048 is used in cell B3. The output to cell B3 is again the same, however it is what happens when the formula is copied down column B that is interesting. The output in cells B4, B5, and B6 is the same as the output in cell B3. Why? The formula is getting its input from the absolute row 3 of the column to the left (in this case A). Formulas in spreadsheets are always preceded by an equals (=) sign. Note also that mathematical operands such as +, -, *, /, and ^ are essentially the same as for a scientific calculator. For example, if we wanted to convert the measurements in column A from feet to yards, we would use the formula =A3/3 in cell B3. At this point, the reader should also note what happens when the input to cell A3 is changed. The output in column B is automatically updated. This is the power and beauty of modern dynamic spreadsheets. Mathematical operations such as the summation of values from many cells can also be handled easily in a spreadsheet. To sum the values located in cells A3 through A6 and put the resulting sum in cell A8, we would type the formula =SUM(A3:A6) in cell A8. Note that we could also use the formulas =SUM(A$3:A$6) or =SUM($A$3:$A$6) to complete this task equally well. The difference in these two formulas is explained by copying this formula to cell B8. The first formula would copy to cell B8 as =SUM(B3:B6) and the second formula would copy as SUM(B$3:B$6). The third fomula would copy to =SUM($A$3:$A$6) giving the same output as cell A8. Logical operations such as IF-THEN-ELSE statements can also be performed by a spreadsheet. Consider the task of deciding whether certain scores on a test either pass or fail. For the sake of argument, let us say that 50 and above is passing and any score below 50 is failing. Figure~\ref{passfail} shows how a spreadsheet can be used to decide which scores are worthy of passing and which are not.
Figure 2: The use of the IF-THEN-ELSE statement.The key formula in Figure 2 is =IF(A3>=50,"PASS","FAIL") in cell B3. Notice how a relative reference is used so that the formula can be copied down column B giving the results as shown. The formula does the following when located in cell B3. It checks whether the value in the cell in the same row to its left is greater than or equal to 50. If it is, it places the word PASS in cell B3. If it is not, then it places the word FAIL in cell B3.
|
