|
Professional's Guide to Robust Spreadsheets With Examples in Lotus 1-2-3 and Microsoft Excel Ronny Richardson 1996 | 301 pages ISBN: 013262320X |
|||
| $29.95 | Softbound print book | Out of print (?) | |
Introduction
The Purpose of This Book
I took my first writing class as a freshman in college. The instructor assumed everyone knew the basics of the English language, so she concentrated on teaching us how to string words together in a coherent manner. This book uses a similar approach to teach you how to "write" worksheets. It will not teach you how to use a particular spreadsheet program. Rather, it will teach you how to write more effective worksheets. I have three goals in mind for your worksheets:
- Make them easier to use. Just because a worksheet performs complex calculations does not mean it needs to be hard to use. With the proper approach and adequate planning, you can make even the most complex worksheet easier to use.
- Make them easier to write. A worksheet often takes a long time to write and debug. Think of it as working on your car. With a few screwdrivers and wrenches, you can perform most maintenance tasks. However, the specialized tools and techniques of a master mechanic make those same tasks much quicker and easier to perform. The same is true of building worksheets: the right techniques can make building any worksheet quicker and easier.
- Make them easier to maintain. If you have ever had to change a worksheet, then you know why worksheets need to be easier to maintain. Maintaining one of your own worksheets is difficult enough, but maintaining a worksheet created by someone else can be a nightmare! With the proper approach to worksheet design and the proper documentation, you can make maintaining any spreadsheet easier.
This book is not the Chicago Manual of Style for spreadsheets. The limitation of the Chicago Manual of Style is that it is simply a collection of rules on what the final product should look like. This book does more than give you rules for designing your worksheet, it gives you the reasons behind the rules. In that respect, it is much more like a book on how to write a term paper.
Who Should Read This Book?
This book is for anyone who designs and builds worksheets, whether for his or her own use or for others to use. Where there is a significant difference between the two programs, the examples are given in both Lotus 1-2-3 and Excel. However, the approaches and most of the techniques discussed in this book are spreadsheetindependent. Not only are these techniques spreadsheet-independent, they are also version-independent. As a result, you can apply these techniques to any spreadsheet running on any computer under any operating system.
This book will not teach you how to use a spreadsheet program; for that, you need an introductory book on the particular spreadsheet program you use. However you know how to use a spreadsheet, this book will teach you how to use it better.
How the Book is Organized
The book is divided into eleven chapters. They are:
| Chapter 1 |
Initial Design. Almost all worksheets require some design work before you sit down and begin coding. You have to decide if a spreadsheet is the proper tool, who should write the model, and what overall design to use. This chapter covers these considerations.
|
| Chapter 2 |
Basic Layout. This is a critical part of the book, developing an overall structure for your worksheets that makes them easy to write and easy to understand. This chapters covers all of the layout approaches that are available to you. It points out the strengths and weaknesses of each approach.
|
| Chapter 3 |
Input Section. Almost all worksheets require the user to input some
information. The way the user enters those data plays a big role in
how accurate the data are and how easy the worksheet is for the user
to use. In addition, the author of the worksheet can verify the data and prevent some errors from slipping into the rest of the worksheet. By making it easier to enter the correct data and by having the worksheet catch common errors, you can make it much less likely that data errors will creep into your worksheets.
|
| Chapter 4 |
Process Section. A worksheet performs the majority of the calculations
in the process section. To a great degree, the nature of those
calculations will define the content of this section. However, there
are still things the worksheet author can do to minimize errors while
making it easier to write and modify this area.
|
| Chapter 5 |
Output Section. The output section of the worksheet presents the
results to the user. It is important that this section give the user
all the information he or she needs, quickly and clearly. Here, also,
the worksheet author can minimize errors and make it easier to add
reports later.
|
| Chapter 6 |
Macro Section. Many complex worksheets are either entirely macrodriven
or use macros to perform major tasks. This book does not teach you how
to program macrosthat would require an entire book for each
spreadsheet program. However, this chapter will give you some hints on
how to lay out your macros so that they are much easier to understand
and modify.
|
| Chapter 7 |
Worksheet Quality Control. Just as the automobile industry needs to
make sure that cars coming off the assembly line work properly, the
author of a spreadsheet model needs to make sure the model works
properly. This chapter will discuss approaches you can use to test and
improve the quality of your worksheets.
|
| Chapter 8 |
Documenting the Worksheet. If you have ever had to modify someone
else's worksheet, then you know how important documentation is. This
chapter explains a number of techniques you can use to make your
worksheets easier to understand.
|
| Chapter 9 |
Worksheet Standards. When you work on more than one worksheet, or when
more than one person is working on the same worksheet, it makes sense
to have standards or conventions to minimize confusion. This chapter
discusses those standards.
|
| Chapter 10 |
Corporate Concerns. Large companies have special concerns about when
and how to use spreadsheet programs. Those special concerns are
addressed in this chapter.
|
| Chapter 11 |
Batch Processing. Often, you need to look at multiple scenarios as you
use a worksheet. This chapter shows you how to make this easier.
|
|
In addition to these chapters, the book has three appendices:
| |
| Appendix A |
Discussion of Selected Lotus and Excel Functions. Building Better
Worksheets is designed to be a general discussion of the
techniques you can use to build better worksheets. However, some of
these techniques require discussing the functions used to implement
them. This appendix documents the functions that are discussed
extensively in this book, which allows the book to be self-contained.
|
| Appendix B |
Documentation for Selected Worksheets. This appendix provides
documentation for each of the worksheets used in the writing of this
book. A couple of the worksheets receive more detailed documentation.
|
| Appendix C |
Tables that correspond to various functions described in Chapter 6.
|
A spreadsheet or spreadsheet program is a program like Lotus 12-3, Quattro Pro, or Excel that lets you enter numbers and formulas. A worksheet is a specific set of numbers, labels, and formulas you produce while working with a spreadsheet. Thus, the file you produce when you save a file in a spreadsheet program is a worksheet. Other names for a worksheet are model and tool. When I refer to the programmer or author of a worksheet, I am referring to the individual who originally wrote the worksheet.
The name of the Lotus spreadsheet program is Lotus 1-2-3. Generally, I will refer to it as Lotus. The name of the company that makes Lotus is Lotus Development Corporation. On the rare occasion when I need to refer to the company, to avoid any confusion between the two, I will refer to the company as Lotus Development Corporation and not just Lotus.
There are two ways to write a formula. Suppose a worksheet author wants to divide a value by the life of an asset. The author could have the user enter the life in cell B10, then include a /B10 in the formula. The author could also just assume the life was ten years and include a /10 in the formula. When an author includes a specific value in a formula rather than prompting the user for the value to use, it is called hardwiring the value.
Figure 1 shows a typical three-dimensional worksheet. A
two-
dimensional worksheet is a worksheet that has just rows and
columns. A three-dimensional (or 3D) worksheet is a worksheet that
adds a third dimension. This particular worksheet consists of three
different pages. The program stores each of those pages in the same
file, and a formula in one page can access data in the other pages
just the same as if they were both in a single page in a
two-dimensional worksheet.
|
 |
A two-dimensional worksheet is like a notebook containing a single sheet of paper. You can place data anywhere on that single sheet, but you are limited to a single sheet of paper. A three-dimensional worksheet is like the same notebook but with more sheets of paper. Information on one sheet of paper can connect with information on another sheet of paper just as easily as if they were both written on the same sheet of paper.
The pages of a three-dimensional worksheet are typically called sheets. The names of the sheets you see at the top of Figure I-1 are called tabs. Each sheet has a single tab, and you change to that sheet by clicking on the tab. Most spreadsheet programs give these a default label consisting of a letter, with A for the first, B for the second, and so on. They typically give you a method to change these names to something more meaningful, as Figure 1 shows.
Showing Lotus and Excel Differences
Typically, Lotus and Excel work very similarly. When the only difference between the two is a slight keystroke difference or the specific syntax for a formula, I will not take up space and your time by writing about each program individually. I will simply discuss Lotus in the text and show how Excel is different by putting its information inside [square brackets] at the appropriate spot.
For example, I could point out that the easiest way to add up a range of numbers is using a formula like @SUM(A1..C3) [=SUM(A1:C3)]. This would indicate that in Lotus you would use @SUM(A1..C3) and in Excel you would use =SUM(A1:C3).
Of course, when there are significant differences between Lotus and Excel that are important to this book, I will discuss each program individually. You can see this in great detail in Chapter 6, which deals with macros.
Getting Copies of the Worksheets Used in this Book
If you are interested in obtaining the worksheet models discussed in this book, you have two options. First, all of the models are available from my home page on the Web. The address is:
This page also gives information about some of the other books that I have written.
Of course, downloading files off the Internet can be time consuming. If you would prefer to get the models by disk, all of the models discussed below are available on a single 3.5-inch diskette for a nominal charge. To order this diskette, send six dollars to the following address:
Ronny Richardson
Building Better Worksheets Diskette Offer
Post Office Box 622M
Quincy, Illinois 62306-0622
Please allow three to four weeks for delivery.
About the Author
I was born in Oak Ridge, Tennessee and raised in Atlanta, Georgia. I have undergraduate degrees in Electronics and Mathematics. I have graduate degrees in Decision Sciences and Business Administration. I also have a PhD in Business Administration. I currently teach in the Division of Business and Computer Science at Quincy University, a small Franciscan liberal arts university in Quincy, Illinois.
I began using a computer in 1983. The next year, I won an IBM clone at the Atlanta Comdex show, which spurred my interest in learning about computers. Later that year, I began teaching computer classes at a local computer store. In 1986, I began writing articles as a freelance author for Computer Shopper. Since then, I have published fifteen computer books and over 350 articles about computers.
I really learned to use Lotus and write worksheets when I spent ten years working as an analyst in a Fortune 100 company. I have refined those skills by doing consulting work and by writing one prior Lotus book.
Last Notes
While I certainly hope it does not happen, it is possible that you will get lost at some point while reading this book, or you will fail to understand something. I have done everything I know of to keep this from happening. If you do get lost, please make a note and drop me a line. Tell me what you did not understand and why. If you are too confused to clearly state the problem, then let me know where I started to lose you.
I have a very selfish reason for asking you to do this. I want to keep this book in print as long as there is a market for it. If you help me spot the confusing portions, I can work on them and make the next edition better. Furthermore, knowing where I failed to clearly explain something will help me as I write other books. I must add the usual disclaimer that I can't always respond to individual letters in case I get a surge of letters. However, I have managed to respond to each letter I have received so far. The address is
Ronny Richardson
c/o Manning Publications Company
3 Lewis Street
Greenwich, Connecticut 06830
DESCRIPTION
This book teaches you how to develop large, bug-free, easy-to-understand, and easy-to-maintain spreadsheets. It explains how techniques that work on small spreadsheets fail on larger projects. Methods from this book can be critical when the developer of the spreadsheet isn't the one using or maintaining it. The Professional's Guide to Robust Spreadsheets is rich with real-life examples in both Lotus 1-2-3 and Microsoft Excel. This book helps you:
- Design for ease of use
- Plan for trouble-free maintenance
- Reduce programming errors
- Write spreadsheets that self-detect and correct wrong data
- Write effective reports
- Bring quality control to your design
- Set organizational standards for spreadsheet design
- Automate routine tasks with macros
WHAT THE EXPERTS SAY ABOUT THIS BOOK...
"I found myself marvelling at how relatively profound points could be
so clearly expressed. This refreshing clarity made the book fun as
well as educational."
--Allan G. Atherton, Computer Consultant
"This is the only book I've read that shows how to write better
spreadsheets, rather than simply use a spreadsheet program."
--Peter J. Luise, President, Renaissance Soluations
ABOUT THE AUTHOR...
Ronny Richardson, now a faculty member at Southern Polytechnic State University, was formerly a financial analyst at a Fortune 100 company where he developed and maintained thousands of spreadsheets, large and small. He has written 20 computer books, including several best sellers.