Revised February 24, 2000
Calculating with 1-2-3
Formulas begin with a plus sign (in Excel they begin with = sign)
Order of operations (precedence)
Adding numbers with the sum icon
Functions begin with @ sign - Class exercise Entering a function (p. 163) or you can simply type it in
After you enter a formula or function in a cell, what you will see in the cell is the value; to see the formula or function, you can look in the formula bar at the top of the screen. Unfortunately, I have not found a way to make the formula bar bigger so that one can more easily see what is there short of reducing the resolution of the display (which you can do from the Start menu, then Settings, Control Panel, Display, Settings).
However, there are several ways to make the text in the spreadsheet bigger -- this will help you find mistakes since it is often difficult to tell the difference between a period and a comma.
1) To make the text on the screen and the printed text bigger, you can change the default font. Click on File, User Setup, 1-2-3 Preferences, New Workbook Defaults, and then set your default font. This changes the default font on spreadsheets you create after that, not the font size on any you have created.
2) To make the text on the screen bigger, but leave the printed text the same size, you change the Zoom. You can do this immediately on the current spreadsheet by clicking View, Zoom to, and then pick a size -- larger is bigger. The choices are 25, 50, 75, 100, and 200. The default on mine is 87. If you want some other choice, say between 100 and 200, you click File, Workbook Properties, and select the zoom level you desire on the View tab. You can click a button on this tab to make this the default for subsequent workbooks.
3. Since you can't make the formula bar bigger, but you can make the cell appear bigger, you may want to edit formulas in the cell, rather than in the formula bar. To edit the formula in the cell, do either of the following:
a. Double click on the cell
b. Hit F2 to edit the current cell
BOTTOM LINE RECOMMENDATION: Change the view to a comfortable size and edit in the cell, not the formula bar, if you have trouble seeing in the formula bar. When you print, if the text is too small for your taste, select all the cells and increase the font size.
----------------------------------------------------------
Moving and Copying formulas
Drag and drop,
Cut, Copy, and Paste menu commands, or
Ctrl X, Ctrl C, Ctrl V keys
Hitting Enter is alternative to Paste or Ctrl V
Copying multiple cells to form rectangular set (not in Wempen)
Absolute versus Relative References - Important
Usually you want relative
Calculating percentages is an example where you want absolute (since total figures into calculation) -- illustrate with primary example - $ notation
The following observations may help you work with formulas and functions:
1) Use of period and comma/semicolon. A period is used to separate cells in a range, e.g. a1.a3 means the range a1 to a3 as in @sum(a1.a3). The period used for the range can be either one period or two; if you enter one period, 1-2-3 adds the second. A comma or semicolon is used to separate the arguments of a function. For example, the @PMT function requires three arguments, so one writes @PMT(20000, 10/100/24, 24) If one wanted the sum of a1 and a3 through a5, one would write @sum(a1,a3.a5)
2) In the homework to develop a schedule of principal and interest payments for a loan, one has to develop a separate formula for each column, beginning with month 1. I would suggest you do the formula for each column before you start copying the formula down the page for the other months. There are two reasons for this.
a) This will help you focus on how the columns are interrelated, before getting into relative vs. absolute addressing issues. For example, a student showed me her figures for 24 months for the "remaining amount owed" column and the numbers differed from what I had on the sheet. We explored various possibilities about whether payments were made at the beginning of the month vs. end of the month. I concluded that I had made a mistake and I promised to look at it the next day and send out an explanation. The next day, I suddenly realized that my spreadsheet was right. The problem was that we were focusing just on a single column. If you look at the other columns, the answer becomes apparent.
b) If one develops formulas for the entire row first, you only need to copy once, rather than a separate copy for each column.
3) When you copy a formula from one cell to another, and the original cell has the right answer but the second does not, look at the formula in the second cell and see what is wrong with it (generally it will be a problem with relative and absolute addressing). Once you figure out was it wrong with it, change the ORIGINAL cell, NOT the second cell, redo the copy, and see if the problem with the second cell goes away.
4) If you are having problems with a formula, it may help if you write down in words the steps in the calculation. (This is recommended when you write a computer program, and it is called pseudocode). When you first write the words down, you may not want to make references to "the current period", "the last period", but as you refine the formula it might be appropriate to add these. For example, if I were to do a formula for the percentage of the vote that Bush got, I would write "divide Bush's vote by the sum of the votes for all the candidates."
Key Aspects of a Spreadsheet (as compared to a calculator)
Entries are stored, which means they can be recalled and modified or used for other calculations
Entries can effectively be labeled by having names in parallel cells
Cell values can be functions of other cells
Cells can be copied to other cells to save time and achieve the desired results -- copy values, copy formulas, either absolute or relative
My uses of spreadsheets
Calculator - balancing checkbook, onetime calculations
Personal finances - taxes, expenses, assets and debts, income
Gradebook for classes -- recordkeeping, calculations
Construct financial models -- e.g. convert traditional IRA to a Roth -- involves the interplay of several assumptions, often over a period of years
Charting/graphing data -- Almost strictly a business use, as opposed to personal
Entries to be sorted
Import material from other applications -- often through comma-separated-value (CSV) format -- for example, the email addresses for this class - Encountered errors in printing address book in Microsoft Outlook, so I exported into CSV format and imported into a spreadsheet
Spreadsheet as the first "killer app" for personal computers - I was surprised to hear this, I had thought word processing would have been