| ECONOMICS |
College of Business and Public Administration
Drake University, Des Moines, Iowa 50311 |
|---|
| Notes by Bill Boal (Rev. 10/97) |
Contents:
A spreadsheet program is a type of computer application first invented in the 1970's for business calculations. The first commercial spreadsheet program was called VisiCalc®. Since then, spreadsheet capabilities have been strengthened to include scientific and statistical analysis, graphics, and simple database functions. Some of the more popular spreadsheets sold today include Lotus 1-2-3®, Microsoft Excel®, and Corel Quattro Pro®. This handout describes some of the features common to all spreadsheets. A few differences between the most popular spreadsheets are also noted.
A spreadsheet is a grid of cells, arranged in rows and columns, whose contents can be linked. Columns are indicated by letters while rows are indicated by numbers. Hence, for example, cell C5 is in column C and row 5. Each cell can contain either text (sometimes called a label ) or a value (a number or an algebraic expression).
A spreadsheet looks like a table, as might be created by a word processor, but it is much more powerful. What makes it more powerful is that the contents of any cell can be linked to other cells if the cell contains an algebraic expression (sometimes called a formula). For example, an algebraic expression might indicate the difference between two other cells in the spreadsheet, or the sum of a column of numbers on the spreadsheet. When a cell contains an algebraic expression, the value of the expression, not the expression itself, is displayed.
Algebraic expressions are identified by their first character. In Microsoft Excel, any algebraic expression must begin with an equal sign (=) or it is interpreted as text. In Lotus 1-2-3 and Quattro Pro, any algebraic expression must begin with one of the following characters: period, +, -, (, pound sign, or $. or the expression is interpreted as text. Conversely, if a piece of text must begin with any of these characters, one can force the program to interpret it as text by inserting an apostrophe (') at the beginning (the apostrophe will not be displayed).
The rest of any algebraic expression must follow the rules of syntax common to many other computer languages. In particular, an asterisk (*) indicates multiplication, a slash (/) indicates division, and a caret (^) separates a base number from its exponent. Operations are performed in the standard order:
For example, if in Microsoft Excel, the cell C2 contains the expression "=A1-B1*B2" then cell C2 will display the difference between the value showing in cell A1, and the product of the values in cells B1 and B2. (Note that if the equal sign were omitted, Excel would interpret the expression as text.) In Lotus 1-2-3 or Quattro Pro, the same result would be displayed if C2 contained the expression expression "+A1-B1*B2". To perform the subtraction first, use parentheses: either "=(A1-B1)*B2" in Microsoft Excel, or "(A1-B1)*B2" in Lotus 1-2-3 or Quattro Pro.
Algebraic expressions are not normally displayed on the screen or printed. Instead, only the computed values are displayed. For example, if cell A1 displays "10," cell B1 contains "2," and cell B2 contains "3," then cell C2 will display "4."
| What's in the cells | What's displayed on the screen | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | A | B | C | D | ||
| 1 | 10 | 2 | 1 | 10 | 2 | ||||
| 2 | 3 | =A1-B1*B2 | 2 | 3 | 4 | ||||
| 3 | 3 | ||||||||
To start building a spreadsheet, simply highlight a particular cell using the arrow keys (or click on it using the mouse), type in the desired text or value, and press "Enter" (or "Return" on a Mac). Then go on to the next cell.
As you type algebraic expressions, you can point to cells with the mouse instead of typing in cell addresses. For example, instead of typing "=A1-B1*B2" directly, you can do the following:
To delete a cell, highlight it and press "Delete". Entire rows or columns can be deleted by choosing the "Delete command" in the "Edit" menu.
Popular spreadsheets can hold hundreds of columns and thousands of rows--far more information than can be displayed on the screen at one time. There are many ways to move from one part of a spreadsheet to another. Arrow keys take you from one cell to an adjacent one. To move in bigger steps, use the "Page up" and "Page down" keys to jump vertically, and the "Tab" and "Shift-Tab" keys to jump right and left respectively. Or use the scroll bars.
Recent versions of popular spreadsheet programs include multiple pages or sheets for each spreadsheet. Pages are accessed using tabs displayed at the bottom off the screen. All cell addresses thus implicitly contain a page reference in addition to a row and a column, but the page reference is not required in algebraic formulas unless the cell is not on the current page.
Recent versions of popular spreadsheet programs can read text files and split them into rows and columns. To do this in recent versions of Microsoft Excel, first open the text file. All the information will be placed in column A. Then use the mouse to highlight the information to be split into columns and then choose "Text to columns..." from the "Data" menu and follow instructions in the dialog box. Lotus 1-2-3 and Quattro Pro have similar routines.
Be sure to save your file in the regular format (as a "Workbook" in Microsoft Excel, for example). If you save in text format, all formulas, formatting, and charts will be lost.
Spreadsheets sold today are intended to produce publication-quality printouts. They all contain a variety of options controlling features like:
How these options are set varies from one program to another. In Microsoft Excel, most of them are set in the "Format" menu or under the "Page setup" command in the "File" menu.
Spreadsheets can be moved between programs and computers if you take a few precautions.
First, remember that recent Macintosh computers can read diskettes in PC or DOS format, but not vice versa. Hence, if you plan to switch computers, you are advised to save your spreadsheets on a diskette in DOS format.
Second, most programs can read spreadsheets created by earlier versions of the same program (and save programs in that earlier version's format), but not necessarily vice versa. For example, Microsoft Excel for Office 97 can read Excel 5 files, but Excel 5 cannot read Excel for Office 97 files. Hence, if plan to work on your spreadsheet with an earlier version of the same program, you are advised to save it in that earlier version's format.
Third, many spreadsheet programs cannot read each other program's spreadsheets, but most programs can read Lotus 1-2-3 spreadsheets. (Lotus 1-2-3 attained this status because of its huge market share in the 1980s.) Hence, if you plan to switch programs, you are advised to save your spreadsheet in one of the Lotus 1-2-3 formats (WKS, WK1, or WK3). Of course, obscure functions peculiar to your program and not available in Lotus 1-2-3 may be lost.
The entire spreadsheet can be created one cell at a time, but is frequently much quicker to use the "Copy", "Cut", and "Paste" commands. These can be accessed by choosing them in the "Edit" menu or using buttons at the top of the screen.
When cells containing algebraic formulas are copied, the cell addresses in their formulas shift right along with the cell being copied. For example, in Microsoft Excel, if cell C2 contains the expression "=A1-B1*B2", and its contents are copied to cell D4, then D4 will contain the expression "=B3-C3*C4" and would probably display a different number from cell C2. Note that the relative addresses are preserved: like the old cell, the new cell contains the difference between the cell one row higher and two colums to its left, and the product of the cell one row higher and one column to its left and the cell immediately to its left. (In Lotus 1-2-3 and Quattro Pro, these formulas must begin with a "+" sign instead of an "=" sign).
To preserve absolute addresses, add $ signs to the cell addresses in the original formula before copying. For example, if cell C2 had contained the expression "=$A$1-$B$1*$B$2" and its contents were copied to cell D4, then D4 would contain exactly the same expression "=$A$1-$B$1*$B$2" and would display exactly the same number as cell C2. You can even preserve absolute row addresses only or absolute column addresses only: the $ sign precedes the part of the address to be preserved.
| Copying without $ signs: relative addresses preserved |
Copying with $ signs: absolute addresses preserved | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | A | B | C | D | ||
| 1 | 10 | 2 | 1 | 10 | 2 | ||||
| 2 | 3 | =A1-B1*B2 | 2 | 3 | =$A$1-$B$1*$B$2 | ||||
| 3 | 3 | ||||||||
| 4 | =B3-C3*C4 | 4 | =$A$1-$B$1*$B$2 | ||||||
Cutting and pasting works the same as copying, except that absolute addresses are always preserved, even if they do not contain dollar signs. This difference reflects a difference in purpose: the "Copy" command is typically used for building a spreadsheet while the "Cut" command is typically used for rearranging it.
Many spreadsheet commands and formulas can perform operations on entire arrays of cells (also called blocks). An array is a rectangular-shaped group of contiguous cells. It is described by the addresses of two cells in opposite corners, separated by a colon in Microsoft Excel or by one or more periods in Lotus 1-2-3 and Quattro Pro. For example, the array "C5:D7" in Microsoft Excel (or "C5.D7" in Lotus 1-2-3 and Quattro Pro) includes the cells C5, C6, C7, D5, D6, and D7.
You can always refer to an array using this syntax, but often it is easier to highlight it with the mouse. Simply point the mouse to one corner, hold the left button down (or the only button on a Mac), and drag the mouse to the opposite corner of the array.
To copy an entire array from one part of a spreadsheet to another,
One cell can also be copied to many. This is useful when an entire column or row must contain the same sort of expression. To do this, the source array should be a single cell, while the destination should be the entire destination array.
Copying or cutting and pasting between different pages of a spreadsheet works exactly like the same operation between different parts of the same page. Copying between different spreadsheets (if more than one is open) is equally straightforward, except if dollar signs are used in algebraic expressions (then the result depends on the particular program).
Some handy copying options are available in the most popular spreadsheets:
(These options are available under the "Copy special" command in Microsoft Excel.)
Most spreadsheets allow you quickly to create a wide variety of very attractive graphic displays of the data in your spreadsheet, including many kinds of data plots, bar charts, pie charts, etc. Two kinds of graphs (also called charts) are especially useful. Both display multiple series of data, arranged on the spreadsheet as rows or columns of an array. Their appearance is similar, but they are constructed slightly differently.
X-Y graphs are scatter plots. This kind of graph plots all series in a block except the first series. The first series, residing in the left-most column, identifies the horizontal (X) coordinates against which the other series are plotted. The numbers in the first series need not be equally spaced or strictly increasing. If the first series is not strictly increasing or decreasing, the plots will double back on themselves. So any mathematical function can be plotted with an X-Y graph. However, the labels on the horizontal axis must be numbers. The series to be plotted need not be contiguous.
Line graphs are time series plots. Each successive point in a series is always plotted a fixed horizontal distance from the previous one. Thus no plot can double back on itself. However, optionally the first series can consist of labels for the horizontal axis, which may be either numbers or text (such as months or countries).
Most programs allow graphs to be stored two ways:
All programs offer a variety of options controlling graphic features like:
How these graphs are created varies from one program to another. In Microsoft Excel, graphs (called "charts" in Excel) are most easily created as follows:
After the "chart" is finished, it can be edited later by double-clicking on it. In Lotus 1-2-3 and Quattro Pro, graphs are created using a separate "Graph" menu (at least in earlier versions).
In addition to the usual algebraic operators, spreadsheet functions typically provide a large number of convenient mathematical functions for use in algebraic expressions. All functions begin with a @ sign in Lotus 1-2-3 or Quattro Pro, but not in Microsoft Excel.
The simplest functions take single arguments, which may be individual cell addresses, or numbers, or algebraic expressions. For example, "=abs(-3)*" in Microsoft Excel (or "@abs(-3)" in Lotus 1-2-3 or Quattro Pro) would display the number 3. Similarly, "=abs(D3-C2)" in Microsoft Excel (or "@abs(D3-C2)" in Lotus 1-2-3 or Quattro Pro) would display the absolute value of the difference between the numbers displayed in cells D3 and C2. Some useful functions taking single arguments include the following.
| Single-Argument Function | Microsoft Excel | Lotus 1-2-3 | Quattro Pro |
|---|---|---|---|
| Absolute value of cell | abs(cell ) | @abs(cell ) | @abs(cell ) |
| Exponential function (2.7182... raised to the power of cell ) | exp(cell ) | @exp(cell ) | @exp(cell ) |
| Natural log (base 2.7182... ) of cell | ln(cell ) | @ln(cell ) | @ln(cell ) |
| Square root of cell | ln(cell ) | @sqrt(cell ) | @sqrt(cell ) |
The number pi = 3.14... is also available as a trivial "function." In Excel it is given by "pi()", a function with parentheses but no argument. In Lotus 1-2-3 or Quattro Pro it is given simply by "$pi&34;.
Other functions take arrays as arguments. (Recall that an array can be described by the cell addresses of opposite corners, or by simply dragging a mouse over it.) Some useful functions taking array arguments include the following.
| Array-Argument Function | Microsoft Excel | Lotus 1-2-3 | Quattro Pro |
|---|---|---|---|
| Number of cells containing numbers in array | count(array ) | @count(array ) | @count(array ) |
| Mean of values in array | average(array ) | @avg(array ) | @avg(array ) |
| Sum of values in array | sum(array ) | @sum(array ) | @sum(array ) |
| Median of values in array | median(array ) | ??? | @median(array ) |
| Minimum of values in array | min(array ) | ??? | @min(array ) |
| Maximum of values in array | max(array ) | ??? | @max(array ) |
| Sum of squares of values in array | sumsq(array ) | ??? | @sumsq(array ) |
| Variance of a population, assuming the entire population is in array | varp(array ) | @var(array ) | @var(array ) |
| Unbiased estimate of the variance of a population, given a sample in array | var(array ) | ??? | @vars(array ) |
| Standard deviation of a population, assuming the entire population is in array | stdevp(array ) | std(array ) | @std(array ) |
| Estimate of the standard deviation of a population, based on the unbiased estimate of the variance, given a sample in array | stdev(array ) | @stds(array ) | |
| Sum of the crossproducts of values in array1 and array2. (Also called "inner product" or "dot product".) | sumproduct(array1,array2 ) | @sumxy(array1,array2 ) | @sumproduct(array1,array2 )
or @sumxy(array1,array2 ) |
| Covariance of array1 and array2. | covar(array1,array2 ) | ??? | @covar(array1,array2 ) |
| Correlation of array1 and array2. | correl(array1,array2 ) | ??? | @correl(array1,array2 ) |
If a cell contains an algebraic expression, then its value depends on the values in other cells. You may want to find the values of those other cells that maximize or minimize the value in the first cell. For example, if a cell contains an expression for the profits from a particular business activity, as a function of the quantity of inputs and outputs, you may want to find the particular quantities of inputs and outputs that maximize profits.
Most popular spreadsheet programs have a routine for doing this. In Microsoft Excel, this routine is called the "Solver" and is located in the "Tools" menu (or the "Formula" menu in Version 4). (The "Solver" is an add-in, not included in the default installation.) In Quattro Pro Version 5, this routine is called the "Optimizer" and is located in the "Tools" menu.
Any optimization routine usually requires you to specify several things.
Optimization often takes a minute or two of computation. The program converges to the right answer quickest if the starting values in the cells to be changed are close to the correct solution.
Optimization is always subject to pitfalls. Sometimes the routine does not find the answer right away and must be coaxed to try again. Often the answer is an approximation that does not match perfectly the solution found by exact methods (such as calculus). Sometimes the algebraic expression does not have an optimum and the program never converges to an answer. Sometimes it converges to the wrong answer (a local optimum rather than a global or over-all optimum). In case of problems, it is wise to double-check the algebraic expression to be optimized and try different starting values.
Most popular spreadsheet programs have routines for computing ordinary (linear) least squares regression. In recent versions of Microsoft Excel, this routine is invoked by choosing the "Data analysis..." command in the "Tools" menu. (If "Data analysis" does not appear on the "Tools" menu, choose the "Add-ins" command and note whether "Analysis ToolPak" has an X in the box next to it. "Analysis ToolPak" is an add-in, not included in the default installation.) In Quattro Pro Version 5, the regression routine is accessed by choosing "Advanced math" in the "Tools" menu.
Any regression routine requires you to specify several things.
Recent versions of popular spreadsheets have many more options, such as:
| Drake home page > CBPA home page > Economics home page > Top of this page |