home *** CD-ROM | disk | FTP | other *** search
-
- ----------------------------------------------------------------
-
- USING SPREADSHEETS
- THAT FEELING OF MONEY AND POWER
-
- ----------------------------------------------------------------
-
- WHAT IS A SPREADSHEET?
-
- One simple definition for a spreadsheet, is: "a storage and
- calculation system which resembles a piece of graph paper." In
- many popular spreadsheet programs this "electronically alive
- grid" can act like a database (storage), calculator
- (accountant's pad) and graph production device (graph output).
- In effect the cells within the grid of this "electronic graph
- paper" can add, subtract, move and manage numbers and
- information.
-
- ----------------------------------------------------------------
-
- DATABASE SPREADSHEET USE OF A SPREADSHEET - AN EXAMPLE
-
- ----------------------------------------------------------------
-
- Study the illustration below then read the explanation which
- follows:
-
- A B C D
- ==========================================================
- 1 || | CLIENT CONTACT DATABASE | |
- 2 || | -------------------- | |
- 3 || | 1987-1988 | |
- 4 || | | |
- 5 ||NAME | DAY TEL | EVE TEL | FIRM |
- 6 || | | | |
- 7 ||Brown, Sam | 232-9090 | 111-7878 | Genl. Mega |
- 8 ||Happ, Sue | 687-9058 | 454-6767 | Whoops Inc |
- 9 ||Davis, Bob | 444-9999 | 343-0909 | Creamy Co |
- 10 || | | | |
- 11 || | | | |
-
- Just as we could keep client names, christmas card lists, key
- suppliers and inventories of goods in a book, we could also keep
- items to remember in a spreadsheet as illustrated. But the power
- and elegance of a spreadsheet is only partially storage and
- retrieval. We can also sort the data by phone number (list of
- all clients sorted by phone number or list of all clients sorted
- by last name). We can also search the data (find all clients
- employed by one firm). We can expand the database (add more
- names) We can expand the fields - columns in the spreadsheet
- (for example we could add yet more columns to the right to
- include address, birthdate, product ordered, date of last order,
- average dollar amount of order, date of first contact, etc).
- Once these new items of data have been added, we could again
- search and sort for various interesting facts (all clients in
- zip code 98040 having more than $2,000 of business with us).
-
- A spreadsheet typically refers to the boxes where information is
- stored as cells. Each cell has an address. Thus in the example
- cell A7 contains the name Sam Brown.
-
- Most spreadsheets are large! Many offer 256 columns and over
- 8000 rows! You scroll or move around to view one screen of data
- at a time. True database programs differ from spreadsheets in
- how they view the information and process it, but for many
- people, a spreadsheet used as a database is a simple,
- understandable and very fast way of storing, searching and
- sorting information. For larger databases of several thousand
- items, you would probably be wiser to use a true database
- product, however.
-
- ----------------------------------------------------------------
-
- ANALYSIS & CALCULATION USE OF A SPREADSHEET - AN EXAMPLE
-
- ----------------------------------------------------------------
-
- Study the illustration below then read the explanation which
- follows:
-
- A B C D
- ==========================================================
- 1 || | CHECKBOOK BALANCING FORM | |
- 2 || | -------------------- | |
- 3 || | Outstanding |Outstanding |
- 4 || | Withdrawals: |Deposits: |
- 5 ||Bal from | | | |
- 6 ||statement: | 1500 | 50 | 25 |
- 7 || | | 50 | 25 |
- 8 ||Sum of all | | 100 | 50 |
- 9 ||outstanding | | 200 | |
- 10 ||withdrawals:| 500 | 100 | |
- 11 || | | | |
- 12 ||Sum of all | | | |
- 13 ||outstanding | | | |
- 14 ||deposits: | 100 | | |
- 15 || | | | |
- 16 ||Total: | 1100 | | |
- 17 || | |
- 18 ||Register: | 1100 <--- When B16=B18, checkbook is balanced!
-
- A spreadsheet can also do calculations. We all have balanced a
- checkbook at some time. Most of us use the printed form on the
- back of the statement. You fill in the boxes with a pencil, find
- missing checks, post forgotten deposits and generally scratch
- and erase your way to a balanced and reconciled checkbook each
- month. The spreadsheet above looks and acts about the same but
- does it more quickly. It adds new checks and erases errors more
- quickly, and when done prints out a final copy on your printer.
-
- When you update an entry on the form, it instantly adds the
- columns to arrive at new totals. Trial and error the easy way!
-
- The results from all numbers in column C are added and placed in
- the cell at position B10. Similarly, the numbers in column D are
- added and placed in cell B14. Finally, a calculation is done to
- produce the result of B6+B14-B10 and placed in B16. This all
- happens in about 1/4 second!
-
- A feature common to many spreadsheets is the ability to create
- graphs of great variety: bar, pie, stacked bar, line and scatter
- graphs are quite commonly obtained from data placed into or
- calculated by the spreadsheet.
-
- Obviously more elaborate spreadsheet examples can be prepared
- which produce projections, budgets, salary expenses, tax reports
- and so forth. Just remember that the spreadsheet in its
- calculation mode can do thousands of calculations quickly and
- can change all cells based on one or two small changes in a few
- cells. Many spreadsheets have special functions for advanced
- statistical, financial, date and real estate formulas already
- imbedded or hidden in the structure of the spreadsheet. Macros
- (a form of multiple step programming or allowing the computer to
- type many keystrokes at once) are a common feature which provide
- yet further shortcuts within most spreadsheets.
-
- Many spreadsheets also have hidden "help screens" which you can
- quickly pop up into view for a refresher on points you have
- forgotten. These help screens are frequently "context sensitive"
- meaning you can stop while working with a spreadsheet formula
- and the help screen can "sense the context" of your location and
- jump immediately to a help screen on formulas!
-
- A spreadsheet obviously demands a sense of organization.
- Experienced spreadsheet users frequently suggest the following
- guidelines:
-
- * Try to create flexible spreadsheets. Using variables in your
- formulas instead of fixed values allows quick and easy
- substitution. A spreadsheet variable is a cell location which
- contains an important number or other assumption. By using a
- variable the underlying assumption can be changed without
- changing all the formulas throughout the spreadsheet. Change
- just one cell instead of many!
-
- * Be consistent from spreadsheet to spreadsheet. Try to keep
- items such as date the spreadsheet was created, author,
- description of task, variables, special reference cells in the
- same place - usually the top left corner for many users.
-
- * Keep things simple. Separating complex formulas and
- calculations into smaller intermediate steps or several cells
- can illustrate the problem and process better than one large,
- poorly documented formula stuffed into a single cell.
-
- * Test the results of your spreadsheet before putting it into
- use and from time to time in case of errors. Manually cross
- check calculations or enter the number "one" into several cells
- so you can mentally "see" that things look correct as results
- total. Try to build cross checking into your spreadsheet.
-
- * Document your spreadsheet with a clearly written purpose,
- procedures and formulas. Imbed this information directly into
- the spreadsheet. Describe where macros, range names, protected
- cells have been placed. Note books or magazines which gave rise
- to the spreadsheet.
-
- * For those with a high degree of interest in spreadsheets,
- a subscription to Lotus Magazine (mentioned in the reading
- list elsewhere in PC-LEARN) is recommended. Back issues
- are goldmines of information!
-
- Some of the best Spreadsheet packages include the following.
-
- Commercial packages:
-
- Quattro Pro. Exceptional character based spreadsheet which runs
- on modest machines and is quite full-featured and richly
- complex. A PC Magazine Editors Choice.
-
- Microsoft Works. This integrated low priced package which also
- contains a database and word processor plus tutorials and other
- goodies is an exceptional value for the beginner. The
- spreadsheet, while not as complex as Lotus or as stunning as
- Excel, is an fine workhorse which also contains a graphing
- package for the small business or home office on a budget. Real
- value many beginners will rarely outgrow.
-
- Microsoft Excel. Graphics based, needs a more powerful machine
- but produces stunning images with varied font and graphics
- effects. Rich and satisfying.
-
- Lotus 123. The old classic spreadsheet warrior, but satisfactory
- if you don't mind paying a little more.
-
- Excellent shareware/low cost and try before you buy spreadsheets
- include:
-
- Aseasyas. Probably the best of the bunch of shareware
- spreadsheet clones. Includes file linking for multiple sheets
- and a rich macro language. Popup menus are intuitive and help
- screens informative.
-
- Cubecalc. A 3D spreadsheet of the shareware persuasion.
-
- PC-Calc. A competent 123 clone from Jim Button who also brought
- us his PC-File and PC-File:DB database shareware packages.
-
- Tutorial finished. Have you registered PC-Learn to receive your
- bonus disks? Registration is encouraged. Shareware works on the
- honor system! Send $25 to Seattle Scientific Photography,
- Department PCL6, PO Box 1506, Mercer Island, WA 98040. Latest
- version of PC-Learn and two bonus disks shipped promptly!
-
-
-