Appleworks Spreadsheet Design
by Tony LaFemina

Published: 7-2-2004


Table of Contents


Preamble

Whoever it was that labeled the spreadsheet as a device for number crunching, really threw the business world a curve. It goes way beyond that. It's a real workhorse. In actuality, a spreadsheet and its associated functions comprise a software generator. Anyone can design their own software using a basic spreadsheet program.

Just about all businesses today run into similar problems. Software! What's out there and will it help? Sometimes searching for the right software can be more trouble than it's worth. Come to think of it, that goes for anyone with a computer. Well! If you have a spreadsheet program, your problem is solved. A good part of it, anyway.

Contrary to popular belief, knowing all the little idiosynchrasies of a particular program has very little or nothing to do with spreadsheet design. While it may have some weight, it isn't a necessity. Spreadsheet programs make use of a basic set of functions, and once you have an understanding of how these functions work, the program you use shouldn't be a major concern. However, it should be noted that certain jobs may dictate the use of one program over another.

Introduction

Spreadsheets! I believe they arose from the ashes of the ledger. For those not old enough, these were books used in business. They consisted of pages containing multiple rows and columns, in which daily transactions were recorded. It would seem understandable that the same design principles used in the ledger would be carried over to the spreadsheet. I have no idea what the first program of this type looked like, but it definitely took bookkeeping to new heights.

Today's spreadsheet programs consist of an assortment of functions that are capable of doing all sorts of wondrous things. The most important of which, is probably taking the spreadsheet well beyond number crunching. Some functions can perform searches, retrieve and manipulate data, and even reduce or eliminate sorting and scrolling. In addition to that, the user can build exotic formulas to do things like make self-aligning columns for form generation or convert numbers to words for printing checks.

Spreadsheet Editing

One of the things many users seem to overlook is the existence of two distinct forms of editing associated with spreadsheet programs. One deals with spreadsheet construction, while the other deals with spreadsheet display. For one reason or another, most users seem to focus their attention on display features which really isn't that important.

Construction
This type of editing is divided into two parts. Both of which deal with an essential part of layout and design strategies. One part is in knowing what's allowed when changing the size of a spreadsheet, adding columns and rows, or relocating cells and cell ranges. The other part requires an understanding of functions and their values under differing conditions. I guess you could say that an understanding of the program regarding this type of editing would be a requirement if you wanted to get your moneys worth.

Display
Editing in this respect is totally cosmetic and is only a requirement for presentation purposes. It has little or nothing to do with what a spreadsheet actually does and should only be a consideration after the spreadsheet is fully functional.

A Simple Programming Language

Most owners of spreadsheet programs don't realize a spreadsheet is more than a glorified calculator. In actuality, their structure consists of a relatively simple framework that makes use of a variety of functions whose results can be configured to create customized software.

Spreadsheet functions form the basis of a fairly strict programming language. Most of these functions contain arguments and produce one of four values. Blank (an empty cell), text, numbers, and logical (true and false). A function's syntax will show which arguments are necessary and which are optional, if any. The arguments themselves are held to the same four values.

Arguments can take on several forms. They can be simple values, cells or cell ranges containing simple values, or cells containing functions that evaluate to simple values. Taking a closer look at this last condition, we could safely assume an argument itself can also be another function. But not in all cases. Still, this gives us the means to develop complex formulas to produce the results we're looking for. Toss in a few common operators (+, -, <, >, =, ≠) and you have enough flexibility to mold and shape a spreadsheet into just about anything your mind can conceive.

One thing you should keep in mind though. As with any programming language, there's no set steps or pattern to follow when designing a spreadsheet. This means, it's possible to get off on the wrong foot, or make a wrong turn, as you progress. When this occurs, it's always a good idea to take a few steps back and look at the overall picture.

Layout and Design Techniques

Layout and design! These two words seem to go hand in hand. A properly designed project becomes a work of art when the layout complements it. Layout and design techniques, as they're called, are sets of guidelines collected over centuries of time that apply to any field of endeavor devised by man. Generally speaking, I don't think this is a subject that can be taught in a classroom. Although, when confined to a specific subject or topic, the possibility does exist. This type of education seems to be more in line with an acquired understanding of a subject over an undetermined period of time. No matter what topic we choose, there seems to be a set of basic rules (or guidelines) that must be followed in order to be creative and productive.

A perfect example of this would be the laser. A fairly modern device that was not constructed by the team of researchers that developed the theory behind it, but by an independent who apparently followed some guidelines of his own and was able to produce a working model.

Not every road leads to success, and the same holds true in the matter of spreadsheet design. The one saving grace is, it's possible to cheat. More often than not, you'll run into situations where some argument will be an unknown quantity, stopping you in your tracks. Most of the time this could be corrected with a little slight of hand on your part, or you may be able to substitute the problem function with another that doesn't contain any unknowns. If, for no other reason, designing your own software will definitely keep you thinking.

Another problem associated with functions and formulas arises when you choose or build the wrong one for the job at hand. It works great when all conditions are met, but fails when limits are exceeded. Most of the time these problems can be overcome. However, it should be noted that when you consistently run into brick walls, it's time to rethink your strategy. In some cases, you might have to go back to the beginning and start over, and others when you just have to stop and call it quits. For the time being, anyway. Sometimes, taking a break might be all that's needed.

Modular Construction
A definite time saver, if ever there was one. Even the simplest spreadsheets should be designed using modular construction. Take the database-style spreadsheets for example. Their only purpose is to store and retrieve data. This type of spreadsheet usually only requires two distinct sections or areas. A Data Input Area (database) and a Data Display Area. Other types of spreadsheets may require one or more Data Calculation Areas in addition to the input and display areas.

Depending on what the spreadsheet is going to do, determines which sections or areas should be incorporated, and the amount of each. For example, one spreadsheet may require more than one input area, while another would require two or more calculation areas. If at all possible, there should only be one data display area, and it should be conveniently located in the proximity of cell A1, since that's where new spreadsheets normally open.

Sample Spreadsheets

Available for downloading are two sample AppleWorks spreadsheets contained in a folder. Neither of which, crunch numbers. They are two different versions of the all-to-common address book. The address book was chosen because of its simple construction, and its similarity to a database. Both spreadsheets contain the same sample data.

Download spreadsheets now

Basic Operating Instructions

    1. Input data can be entered into the database in any order.
    2. Zip codes containing leading zeroes are entered differently in Address Book 1 (01234 would be entered as, ="01234"). Address Book 2 corrects 3 and 4-digit zip codes automatically.
    3. Enter a search string into cell A1 to search through the database.
      • Address Book 1 requires full first or last names in cell A1.
      • Address Book 2 will search for 2-letter state codes or minimum 3-character strings entered into cell A1.
    4. Enter a location number into cell A2 when requesting data from a known entry.

The purpose for two similar spreadsheets is to:

    1. Show different approaches to laying out and designing spreadsheets.
    2. Show differences in design constructs.
    3. Show how spreadsheets can be modified.
    4. Show those who are unfamiliar with spreadsheets, what they're capable of.

Both address books contain examples of "bad programming" and could be designed better. However dispite these shortcomings, they're both functional when used correctly, and you won't find anything faster or easier to use. Commercial or otherwise.

Some Design Differences

Some Design Similarities

Only minor changes to functions should be all that's necessary for conversion to another spreadsheet program. Anyone looking to hone their design skills or simply pick up some tips may find something of interest. Anyone unable to modify either spreadsheet and wants to actually use it can e-mail me below for a modified copy.

Contact Author

If you need to contact me, you can reach me at remacs@optonline.net. Anyone wanting to distribute this document, in whole or in part, should contact me for approval beforehand.

- Tony LaFemina



Copyright © 2004, Tony LaFemina