OpenOffice.Org 1.0 Resource Kit

The previous section, Formula Basics, provides the basic information necessary for entering formulas. This section expands on that by highlighting tools that help automate formula entry, going into more detail on using cell references, and showing a couple of power user formula procedures.

Function Overview

Calc makes the formula entry process easier and more powerful by providing a library of functions. Functions are keywords like SUM, SQRT, and IF that let you perform specific tasks .

For example, the SUM function lets you add an entire range of cells. Without the SUM function you'd have to enter =B1+B2+B3+B4+B5 to add up the contents of those cells . With the SUM function, you can write the same formula as =SUM(B1:B5). Because functions are so useful, powerful, and essential in creating spreadsheets, Calc comes with a veritable boatload of them; functions that do everything from figuring sums and square roots, to letting you set up conditions within a cell that can make the cell display different values depending on whether the condition is true or false. (That's the IF function). There's even a function to help you figure out monthly payments on a loan (the PMT function).

The following sections highlight useful functions and tools.

Quick Sum

Calc lets you add up rows or columns with a mouse click.

1. Click the cell that you want to contain the sum of the row or column.

2. Click the Sum icon on the function bar.

   Calc automatically enters the sum function and the appropriate range of cells in the row or column, as shown in Figure 20-1.

   Figure 20-1. Doing a quick sum

3. Make sure the range of cells is correct, change the range if necessary, and press Enter.

Changing the Range Selection for a Sum

You don't have to be directly in the row or column you want to add up. You can click any cell, click the Sum icon, and change the range of cells simply by dragging through the range of cells you want to include. You can also select non-adjacent cells and cell ranges by holding down the Ctrl key while you select.

Inserting Subtotals Automatically

If you have a column of numbers you plan on adding up, Calc has a tool to let you insert automatic subtotals after selected rows to generate a running balance. Figure 20-2 shows what subtotals look like.

This feature inserts rows for the generated subtotals. So if you have links set up to cells prior to inserting subtotals, references to those cells could be thrown off after you insert subtotals. For example, if a formula points to cell $A$5, the data in that cell could get bumped to a different cell address by the insertion of subtotals, making the formula point to the wrong cell. (See Using Cell References on page 568 for information that can help you avoid this problem.)

The moral of the story is that if you want to insert subtotals, be aware of the potential impacts of inserting extra rows.

1. Select the area of data for which you want to create subtotals.

2. Choose Data > Subtotals.

3. In the Subtotals window, set the options you want. Use Figure 20-3 for guidance.

   Figure 20-3. Setting subtotal options, tab 1

   In the Subtotals window, you can add more layers to the data groupings (creating groups within groups) by entering the appropriate settings in the 2nd Group and 3rd Group tabs, as well. In the example in Figure 20-2, the data is grouped by date and the dollar amounts are subtotaled, as the settings in Figure 20-3 indicate .

4. Click OK.

Setting up subtotal options is a logical process. For example, you wouldn't want to subtotal a column containing names . The Subtotals tool will let you set any options you want. Just be aware that if the results look strange , you may have to go back and think through the setup process.

When you create subtotals, Calc also adds outline functionality, letting you expand and contract groups, as shown in Figure 20-2.