home *** CD-ROM | disk | FTP | other *** search
- >a3:" LESSON4 -- Replicating Formulas, Inserting, Deleting & Moving
- >a6:" In Lesson2 we learned how to enter numbers, labels, and
- >a7:" formulas into the spreadsheet. In this lesson we will examine how
- >a8:" formulas are 'replicated'. Also, we will see how lines and columns
- >a9:" can be inserted, deleted, and moved. We will be using a spreadsheet
- >a10:" that analyzes 'Cash Flow' for a small business.
- >a12:" Move the cursor to line 11 and type the CREATE TWO WINDOWS
- >a13:" Command:
- >a14:" Control--x 2
- >a15:" Do not synchronize the windows.
- >a16:" Move the cursor to the top window, using the OTHER WINDOW
- >a17:" Command:
- >a18:" Control--x o (small letter 'o')
- >a19:" Again, you will need the 'Other Window' commands to scroll the lesson.
- >a21:" With the cursor in the top window type the FIND FILE Command:
- >a23:" Control--x Control--f
- >a25:" Type the filename 'b:cash.pc', followed by a carriage return.
- >a27:" Perfect Calc displays a sample 'Cash Flow Assessment' program
- >a28:" in the top window.
- >a30:" Let us begin by examining the entire spreadsheet.
- >a31:" When you have seen the entire spreadsheet, position the cursor
- >a32:" at entry box 'b10', which records "Total Cash Flow In" for January.
- >a33:" The formula which displays in the Prompt Line is:
- >a35:" Formula: sum(b5:b8)
- >a37:" This formula instructs Perfect Calc to add the entries in the
- >a38:" previous four entry boxes ('b5' to 'b8') and to display the result in
- >a39:" in 'b10'.
- >a40:" Move the cursor one space to the right to the entry box which
- >a41:" records the same information for February. Here the formula is:
- >a42:" Formula: sum(c5:c8)
- >a43:" Except for their variables the two formulas are identical. Move
- >a44:" the cursor across line 10, noticing that the formulas differ only in
- >a45:" the coordinates that specify their individual column positions. This is
- >a46:" a common way of structuring consecutive formulas--varying them slightly
- >a47:" to account for their positions on the spreadsheet. Entering a series
- >a48:" of such formulas could prove tedious, were it not for a procedure which
- >a49:" Perfect Calc provides called 'replicating'. Let us see how it works.
- >a51:" Let us enter a similar formula into Line 18, which computes
- >a52:" 'Total Cash Flow Out' for each month of the year. Move the cursor to
- >a53:" position 'b18' {To get there type Control--v}. The formula to be
- >a54:" replicated will be:
- >a55:" b18 = sum(b13:b16)
- >a57:" Like 'Total Cash Flow In', this formula will be duplicated to
- >a58:" every entry box in line 18, its coordinate variables changing to
- >a59:" reflect the various column positions.
- >a61:" REPLICATING A FORMULA
- >a63:" With the cursor in position 'b18' type the initial formula for
- >a64:" this position. Begin with an equals sign ( = ):
- >a65:" FORMULA: b18=sum(b13:b16)
- >a66:" Enter this formula with a carriage return. Next type the
- >a67:" COPY ENTRY Command:
- >a68:" Control--W
- >a69:" Perfect Calc saves the formula in a temporary storage space called
- >a70:" the 'Save Buffer' for later duplication to other locations.
- >a71:" Set an invisible mark at position 'b18' using the MARK SET
- >a72:" Command:
- >a73:" Escape. . .<space bar>
- >a75:" Perfect Calc responds: "Mark set at b18" .
- >a76:" This 'mark' defines the beginning of the 'region' over which
- >a77:" the formula will be replicated.
- >a78:" Move the cursor to the end of line 18 using the END OF LINE
- >a79:" Command (Control--E). The new position of the cursor defines the
- >a80:" other boundary of the region.
- >a81:" Replicate the formula across the region of entry boxes in line 18
- >a82:" by giving the multiple YANKBACK Command: Escape...y
- >a83:" At this point Perfect Calc needs to know only one more thing: whether
- >a84:" or not the two variables in the formula are 'relative'. That is, should
- >a85:" they be changed to reflect the positions of the various entry boxes to
- >a86:" which the formula will be 'replicated'? It asks this by displaying the
- >a87:" formula in the Prompt Line and 'bracketing' the variable in question:
- >a88:" b18 = sum( >b13< :b16). . .Relative?
- >a89:" Here, the variable 'b13' is bracketed. The answer, of course, is 'y'
- >a90:" for yes. The question is asked for 'b16'. Again the answer, 'y'.
- >a92:" Perfect Calc immediately replicates the formula to the entry
- >a93:" boxes in line 18, February to December. At each entry the value '0'
- >a94:" is now displayed, indicating that so far no cash has been paid 'out'
- >a95:" for any month. Move the cursor backward along line 18 to see the
- >a96:" replicated formulas.
- >a98:" Replicating is a simple and easy procedure that you will use
- >a99:" again and again in building your spreadsheets.
- >a101:" INSERTING
- >a102:" Inserting a line or column is also easy to do. It is usually
- >a103:" employed when making room for additional data that is either new or
- >a104:" was forgotten when the spreadsheet was created.
- >a106:" For example, suppose that we wished to add a line recording
- >a107:" phone expenses under 'Cash Flow Out'. The line should be inserted
- >a108:" somewhere WITHIN the range of expenses already present, perhaps
- >a109:" between 'Supplies', line 15, and 'Other', line 16. Therefore,
- >a110:" position the cursor on entry box 'a16', where 'Other' is displayed.
- >a111:" Type the OPEN LINE Command:
- >a112:" Control--o (lowercase letter 'o')
- >a114:" Perfect Calc inserts a blank line, shifting 'Other' and all
- >a115:" lines below it down one line. 'Other' has become line 17, 'Total
- >a116:" Cash Flow Out' line 19, etc. All formulas contained in these
- >a117:" lines have been modified to reflect their new line positions.
- >a119:" With the cursor at the beginning of the new line 16, press the
- >a120:" space bar 3 times and type the label 'Postage'.
- >a121:" It should be emphasized that the new line has been completely
- >a122:" integrated into the spreadsheet. That is, the formula in the spread-
- >a123:" sheet which referenced this range of lines ('Total Cash Flow Out') has
- >a124:" automatically been altered to include the new line. Move the cursor to
- >a125:" entry box 'b19'. The original formula was:
- >a126:" b18 = sum(b13:b16)
- >a128:" After the new line was inserted, this became:
- >a130:" b19 = sum(b13:b17)
- >a132:" Perfect Calc will automatically alter formulas that compute a
- >a133:" range of entries, and among which a new line has been inserted. Of
- >a134:" course, if the line is inserted outside the range, then the formula
- >a135:" will have to be altered manually to take account of the insertion.
- >a137:" For example, if we had inserted 'Postage' BELOW 'Other', at line
- >a138:" 17, the formula which computes 'Total Cash Flow Out', and which
- >a139:" originally included only values in the range 'b13' to 'b16' would
- >a140:" have had to be manually altered to include position 'b17'.
- >a142:" Inserting a column is performed in a similar fashion to inserting
- >a143:" a line except that Perfect Calc opens a new column, instead of a line,
- >a144:" at the position of the cursor. With the cursor in column 'b' type the
- >a145:" OPEN COLUMN Command:
- >a146:" Escape...o {the letter 'o'}
- >a148:" Perfect Calc inserts a new column 'b' moving all the other columns
- >a149:" over 1. What was in column 'b' has been moved to column 'c', what was
- >a150:" in column 'c' has been moved to column 'd' and so on.
- >a151:" DELETING
- >a152:" Deleting a line or column from a spreadsheet is as easy as
- >a153:" inserting a line or column. Let us suppose that we wish to delete
- >a154:" line 14, 'Utilities'. Move the cursor to any entry position on this
- >a155:" line and type the DELETE LINE Command:
- >a156:" Control--c
- >a158:" Perfect Calc deletes the line, shifting all lines below the deletion up
- >a159:" by one and renumbering them. As when inserting a line, all formulas
- >a160:" within these lines are adjusted to reflect their new positions.
- >a162:" It is possible to delete not only lines, but single entries,
- >a163:" columns, and even regions of data. Let us delete the new column we
- >a164:" inserted in column 'b'. Move the cursor to any position in column 'b'
- >a165:" and enter the DELETE COLUMN Command:
- >a167:" Escape...c
- >a169:" Details for other delete operations are provided in your Perfect Calc
- >a170:" User's Guide.
- >a171:" We should note that deleting data may at times entail some risk,
- >a172:" because the chance exists that the deleted line or column is being
- >a173:" referenced by some formula variable in another part of the spreadsheet.
- >a174:" When this happens, the referencing formula may compute to 'Error!' at
- >a175:" the next recalculation. The formula is, in fact, left with a 'blind'
- >a176:" reference to a non-existent line or column. Other formulas which
- >a177:" reference THAT formula will also begin computing to 'Error!' There-
- >a178:" fore, it is always a good habit to check carefully for formulas which
- >a179:" might reference the data about to be deleted, and to alter them
- >a180:" accordingly before any deletion is made.
- >a181:" YANKBACK
- >a182:" Perfect Calc temporarily saves every deletion in its 'Save Buffer', a
- >a183:" reserved space in computer memory. It is therefore possible to restore
- >a184:" the most recent deletion from this buffer using the YANKBACK Command:
- >a185:" Control--y
- >a186:" For example, let us delete the 'Supplies' line by moving the cursor
- >a187:" to line 14 and entering: Control--c.
- >a188:" -- Do not make any further deletions, since these will replace the
- >a189:" 'Supplies' Line being held in the 'Save Buffer'. Look at the result.
- >a190:" -- Type the YANKBACK Command : Control--y.
- >a192:" Perfect Calc immediately restores the 'Supplies' Line, shifting
- >a193:" all lines below this down by one and renumbering.
- >a195:" Deleted entries, columns, and regions of data can also be restored
- >a196:" in this manner.
- >a198:" (For more complete details see your User's Guide.)
- >a201:" MOVING DATA
- >a202:" The Procedure for 'moving' lines and columns to new locations
- >a203:" is IDENTICAL to that we have just seen for restoring a deletion,
- >a204:" except that the material is restored from the Save Buffer to a
- >a205:" DIFFERENT location in the spreadsheet. To see how this works, let us
- >a206:" move 'Postage' from line 15 to below 'Rent' at line 14.
- >a208:" First, delete 'Postage' with the DELETE LINE Command: Control--C.
- >a209:" -Then, move the cursor to the 'Supplies' line, below 'Rent'.
- >a210:" -Reinsert the line using the YANKBACK Command, Control--Y.
- >a212:" END
- >a213:" This concludes Lesson4. Be sure to save the modifications you have
- >a214:" to the 'cash.pc' file, since you will be using this file in Lesson5.
- >a215:" Enter:
- >a216:" Control--x Control--w and supply the filename 'cash1.pc'.
- >a218:" Lesson5 deals with copying data between spreadsheets. If you want to
- >a219:" proceed with Lesson5 now, then clear the computer's memory and read in
- >a220:" the 'b:lesson5.pc' file.
- >a222:" If you do not wish to continue, type the QUIT Command:
- >a224:" Control--x Control--c
- >a226:" Answer 'yes' to Perfect Calc's question: "Ignore changes this
- >a227:" session?"
- >a229:" END OF LESSON 4
- >a1