OOo Off the Wall: Tabling the Notion, Part 2

Table formulas--or should I say formulae?--are one
of OpenOffice.org Writer's unique features. Writer uses a formula bar
similar to the one in OpenOffice.org Calc, but with a more limited set
of options. The syntax for table formulas is similar to spreadsheet formulas, with just
enough differences to be frustrating to a spreadsheet expert. Yet,
despite these apparent shortcomings, table formulas are a welcome addition to the
Writer toolbox. As a practical example can show, with a little planning, you
can use Writer's table formulas to build surprisingly complex
documents and, more importantly, to reduce the boredom of routine tasks.
Formula Bar
Formulas are entered in Writer tables using the formula bar. The
formula bar is available by selecting View -> Toolbars -> Formula
bar. More likely, though, you will find pressing the F2 key to be a more
convenient way to access the formula bar. The
formula bar can be used for adding a formula either to a table cell or
to regular text.
Figure 1. The formula bar in Writer closely resembles the
one in Calc.
The formula bar consists of four items:

• The input line, in which formulas are entered either manually or
  through your selections.
• The Formula button, which opens a drop-down list of basic formulas, such
  as Sum, Percent and Square Root. Several categories of formulas also are
  available. In all, some two dozen formulas are available. They include
  most of the functions that basic users are likely to want. In addition,
  you also can enter manually +, -, * (for multiplication) and / (for division).
• The Cancel button, which closes the formula bar. Anything in the input
  line is lost when you select this button.
• The Apply button, which either gives the result of the formula or
  an error message. Unfortunately, the error message is simply
  Expression is faulty rather than an explanation of what you
  did wrong.

Table Formulas vs. Spreadsheet Formulas
Writer table cells and formulas behave much the same as spreadsheet
cells, but with a number of important differences. Like spreadsheet cells,
table cells have addresses that consist of a letter for the column and a
number for the row. In both cases, addresses begin in the top left cell,
which is A1. In both cases, too, you can enter a range of addresses by
entering the start and end address with a colon between them. For example,
A2:D4 includes all the cells between A2 and D4 inclusive.

Whereas a spreadsheet is forgiving if you enter addresses with
lower-case letters, however, Writer tables require upper-case letters only. If
you enter a2:D4, OpenOffice.org Calc reads it without
problem. Writer, however, returns an error message.

As in a spreadsheet, Writer formulas begin with an equal sign (=). So long
as a range of addresses is used, the formulas look much the same. For
instance, if you wanted to add the results of cells A1 and A2 in Calc, the
formula in both Calc and a Writer table would be =SUM(A1:B1),
although Writer for some reasons would enter it without capitalizing the
first letter. However, if you wanted to total two cells that were not next
to each other, in Calc the formula would be =SUM(A1;A5), but in a Writer
table it would be =sum <A1>+<C2>--and would need to be entered manually.

These differences can be frustrating if you're used to working with
spreadsheets. The trouble is the differences are slight enough to forget
but important enough to cause difficulties. And with some Writer table
functions, such as Round, the syntax is different enough that you
probably will need the patience to learn through trial and error and to refer
to the on-line help. Why the syntax isn't identical to that of Calc is
uncertain, but be aware that it can savage you, especially when you're
in a hurry.
Creating an Invoice
To see the practical use of formulas in Writer tables, imagine that
you run a home repair business for computers. When you bill a customer,
you ordinarily send out a two-page document. The first page is a general
letter, with varying degrees of politeness, depending on how late the
bill is. You could use an input list field, if you wanted, to select
the phrases for each stage of lateness. On the second page, you would
present an itemized invoice. If you chose, you manually could enter the number of
hours for each service and the hourly rate, adding them with
a calculator. However, you can save time by automating your invoices
with the following process:

1. Create a table with 4 columns and 12 rows. In OOo version 1.x, the command
   is Insert -> Table. In version 2.0, it's Table -> Insert -> Table.
2. From left to right, label the column headings Service, Hours,
   Hourly Rate and Cost.
3. Select the Hourly Rate and Cost Columns. Then, set the number format
   to currency by selecting Number Format from the right-click menu. Be
   sure to select a format with two decimal places.
4. If you are using OOo 2.0, select the Hours column and set the number
   format to Number. You don't need this step if you are using version 1.x,
   because it has number recognition turned on by default. The default was
   changed in version 2.0 because it often frustrated new
   users.
5. In the last three rows of the Hourly Rate column, enter from top to
   bottom Subtotal, Sales Tax and Total. Right-align the three entries
   either by defining a paragraph style for them or by using the manual
   Align Right button in the tool bar.
6. Enter the Hourly Rate in C2, the second row of the Hourly Rate
   column. For this example, let's assume a standard rate of $65 per
   hour. Notice that because the number format is set you need to enter
   only 65. The dollar sign and decimal points are entered
   for you.
7. The Cost is the number of hours multiplied by the hourly rate. To
   have Writer calculate the Cost for you, place the mouse cursor in D2,
   the second row of the Cost column. Then, press F2 to open the formula
   bar.
8. Select table cell B2 with the mouse. <B2> is entered in the
   input line of the formula bar. Add an asterisk (*) in the input line,
   then select cell C2, followed by the Apply button, the green check mark
   next to the input line.

Cell D2 now contains the result of multiplying the contents of cells B2
and C2. The formula is =<B2>*<C2>.

Since B2 is empty, D2 reads $0.00. If you change either
the hours (B2) or the hourly rate (C2), then D2 is updated
automatically.

This operation copies the formula into the other rows. Because addresses
in table cell formulas are relative, it also updates the formula in the
row. For example, if you pause the mouse over cell B4, you can see that
its formula refers to row 3, not row 2.

In a spreadsheet, as you may know, you could select the cells and
drag with the mouse on the lower-right corner to achieve the same
result. However, Writer table cells are less versatile than spreadsheet
cells are.

Cell D10 now contains the formula =sum <D2:D9>. The colon
between the cell addresses indicates a range of cells, that is, D2 to
D9 inclusive.

This is a second-generation formula composed of the results of all
the other formulas in column D. If any hour or hourly rate in the table
changes, the subtotal is updated automatically.

Don't worry if some of the rows for services are blank. If no hours are
entered, the formula in that row is set to zero.

Figure 2. Writer includes only basic formulas, but they are enough
to create many complex, time-saving documents.
The table now has four generations of formulas. Each is updated
automatically when the number of hours or the hourly rate is changed.

This is a lot of work to do each time you create an invoice, so naturally
you want to save the document containing the invoice as a template,
using File -> Templates -> Save. That way, the table is ready any
time you need it. In each case, you simply need to enter the hours and
hourly rate--no calculations are necessary.

If you ever need a longer invoice, all you would need to do is add
extra rows above the Subtotal and then copy and paste one of the existing
rows, as you did when originally creating the invoice.
Conclusion
Formulas in Writer tables are limited compared to spreadsheets and
different enough that experienced spreadsheet users often find them
hard to use. In fact, if you need anything more than basic functions
or only use them occasionally and don't feel like relearning them each
time, you may prefer to use Insert -> Object -> OLE Object to import a
spreadsheet into your Writer document instead. With a few formatting
options, such as making cell borders visible, the imported spreadsheet
can look identical to a Writer table.

However, if your needs are simple or you're one of the many for whom
spreadsheets seem too overwhelming to consider, Writer tables may be all
you need. Unlike importing a spreadsheet, using table formulas doesn't
require maintaining two separate documents. And, with a little patience
and a template, they are another useful tool for automating your work.

Bruce Byfield is a freelance course-designer and instructor.
He is a regular contributor to Linux Journal and Newsforge.