Articles and Information
by Members for Members

Spreadsheets revisited

by Lee Alexander

To paraphrase dictionary definitions: to calculate is to know where you stand. We calculate our taxes; we take calculated risks (hopefully not with the tax code). When you first come across a computer spreadsheet the general inference is it is an instrument for financial reporting. That is, indeed, its most common usage and a compelling strong point. However, if we expand "know where you stand," we can see many more uses of such reporting beyond dollars and cents.

When you first open a computer spreadsheet you are confronted with a screen full of boxes, boxes seemingly too small to hold anything relevant, plus toolbars with a few unfamiliar buttons. The application we will be discussing is the version of Excel that comes with Microsoft's office XP, SBE. The boxes are called cells and they are arranged in numbered rows and lettered columns. The cells are addressed by lettered column followed by a numbered row, as in A1, C3, etc. A range of contiguous cells is denoted by the first cell followed by a colon followed by the last cell, as in B3:G7. A handy feature is to give a range a name, e.g. “Monthly expenses," to avoid having to remember cell addresses and possible typos. Excel, like other high-end spreadsheet applications, uses a workbook format capable of containing many worksheets for an .xls file. The files in the spreadsheet application in Microsoft's Works (.wks) contain only one worksheet. The number of cells in the worksheet is contained in 65,536 rows and 256 columns, for a total of almost 17 million cells. It is hard to imagine any home-use "database" that the application could not handle. The size of the spreadsheet file is more dependent on the contents of the cells rather than the number of cells used.

An important feature of the application is the two modes of addressing cells, RELATIVE and ABSOLUTE. Relative addressing allows us to fill in cells with incrementing formulas; Absolute addressing allows us to refer to a fixed cell. To refer to a fixed cell we prefix the column and row with a dollar sign ($). I think an example will best illustrate this:

Let us say your spreadsheet contains dates in column A and the sticker price of items purchased in column B. To get the actual price paid we must add the sales tax. Assuming a sales tax of 7%, we enter the formula = .07*B1+B1 in cell C1. If we have five entries of date and price in A1:B5, we can select the range C1:C5 and use the keyboard shortcut <Ctrl> D (for Down) to fill it with incremented formulas. The formula in C2 will use B2 instead of B1. Now if the tax rate changes, say (optimistically) to 6%, we will have to go back and rewrite the formula and again fill in the C1:C5 range. However, if we select an arbitrary cell, F1, and enter a tax rate there, we can refer to that cell with absolute addressing and thus update our spreadsheet with a single entry, if and when the tax rate changes. Our new formula would read =$F$1*B1+B1 and prevent the F1 cell being incremented to F2 as we fill in the range C1:C5.

This screen shot shows some of the tool bars available within the program. The blue bar at the top is the Title Bar showing the application name and the file name (the default, Book1, is waiting for you to Save As … with a more meaningful moniker). Next we have the Menu bar, with most items common to Microsoft applications. Note the substitution of a Data button in place of Word's Table button. The next row is the Standard toolbar. Buttons specific to the application include the Sum function ( S), a Sort button, and the Chart Wizard. The next row is the Format toolbar, almost the same as Word's Format toolbar. The next toolbar is specific to Excel and is called the Function toolbar. Clicking on the function symbol, fx, will bring up a dialog box of formulas. It is this ever expanding collection that makes Excel so powerful. In the Date/Time category, the functions can be very useful in setting up a calendar of tasks.

For example, to reserve the Library's meeting room you cannot call in more than 45 days in advance. Setting up a year's worth of reservations is trivial in an Excel or Works spreadsheet.

The table is generated by the formulas shown in the screen shot below. It demonstrates the ease of doing arithmetic on dates. I chose 43 days earlier than desired date rather than 45 to give me leeway and avoid weekends.

The next example is a shortened excerpt from my Address worksheet. The actual file has over 180 entries. A key part of this worksheet is the inclusion of the Category column. By turning on the Auto Filter in the Data menu, you can select a category to show only the entries in that classification, such as magazines. This would be handy to send a form letter of an address change to update your subscription information. Similarly, you could select a geographic location (town or state) for an abbreviated list of neighbors, for example.

The next worksheet is that for a simplified checking account register.

The column heading, REC, is for Reconciliation – balancing the worksheet against your bank statement.

The worksheet for this procedure is below. To enter the checkbook balance, I recommend copying it from the Check Acct. worksheet (<Ctrl> C) and using Edit | Paste Special | Value. You can't use simple paste (<Ctrl> V) as it is in the form of a formula on that worksheet.

The next example is a medical record keeping worksheet. In my case, I keep an Excel file named Med_Dent.xls with separate worksheets for medical and dental records. This is handier than trying to track separate insurance carriers on a single worksheet. It also facilitates things at tax time.

By the way, Intuit has recently produced a program – Quicken Medical Expense Manager for Windows at $49.95. No doubt it has a few bells and whistles to make life easier. But at fifty bucks, I think its value is questionable if you have an advanced spreadsheet program.

With the popularity of digital photography, adding photos to an Inventory worksheet has never been easier. In some cases it is easier to scan an instruction manual illustration (as in the case of the Porter-Cable saw) rather than set up for a photo session. Either way, it is sure to impress an insurance adjuster if the need should arise.

The next two examples are typical number crunching methods for generating active tables. They use the concepts of Absolute and Relative addressing, as well as a time-saving and powerful feature – array formulas.

The formula used for the first table, Mortgage Loan Analysis 1, is =PMT((B10:B16)/12,$D$6,-$D$7)
The range, B10:B16, is the seven different mortgage rates divided by 12 to get a monthly payment ; $D$6 is Nper, the number of periods (in this case, months) of the term; and the negation (minus sign) of the amount, $D$7 – we wish to end up with a balance of zero.

  In order to fill the table, you select the range C10:C16, enter the formula and its parameters. Clicking on the function symbol, fx, will bring up a dialog box for the formula and facilitate the entry of the arguments. To make it an array formula, hold down <Ctrl> <Shift> as you hit <Enter>. You will then see the formula enclosed in “curly brackets,” signifying its array status.

The second example, Mortgage Loan Analysis 2, simply uses a fixed row of periods instead of a single value. Its formula is =PMT ((A10:A16)/12,(B$9:E$9),-$D$7). In this instance you select the range B10:E16 for the table. If you err and just hit <Enter>, hit function key <F2> to edit the formula and then immediately hit the combination <Ctrl> <Shift> <Enter> to make it an array formula.

There are many, many other aspects to Excel; items that could take months to grasp. We have not even touched on things like Charts, Pivot Tables, statistical functions (correlation, standard deviation, and on and on), and macros – home-made mini programs to simplify repetitive tasks. My approach is to try to get a handle on features as the need or opportunity arises. After years of use and perusal of books on the subject, I still feel I am a novice at using some of the esoteric capabilities of this powerful program. From February to April 15 th of each year, the descendent of Dan Bricklin's brainchild, VisiCalc, gets a good workout on my PC.