Working with Formulas

Calc includes a large number of formulas. These can be applied principally to numerical data, but some also work with other kinds of data, too. This component is actually a continuation of the AutoPilot system, and some of the functions are available in other OpenOffice.org programs.

In addition to simple and complex math functions, there are a range of logical functions, as well as statistical and database tools. Certain formulas can also be used to manipulate text strings such as dates.

You can get an idea of the available functions by clicking the AutoPilot: Functions button on the Formula bar (just below the Object bar). This will bring up a categorized list of formulas, along with brief outlines of what function the formula performs. If you would like more details, use the help system, which contains more comprehensive descriptions of most of the formulas, complete with examples of the correct syntax.

You can reuse formulas simply by cutting and pasting them. Calc is intelligent enough to work out which cells the transplanted formula should refer to, but it's always a good idea to check to make sure the correct cells are referenced.

Using the AutoPilot

To use the AutoPilot to add a function, click the AutoPilot: Functions button on the Formula bar and select the desired type of formula from the Category drop-down list, and then double-click an entry in the Function list to select it. Following this, you'll be prompted to input the relevant figures or define the appropriate data sources. Next to each text-entry box is a "shrink" button, which temporarily hides the AutoPilot window, so you can select cells to be used within the formula.

Let's look at a quick example of using the AutoPilot to work out an average value of a number of cells.

1. Select the cell in which you want the result of the formula to appear.

2. Start the AutoPilot by clicking the AutoPilot: Functions button on the Formula bar. In the left-hand list of functions, double-click AVERAGE. The AutoPilot will then present a list of fields on the right-hand side of the dialog box, where you can enter the values to be averaged. Theoretically, you could type numeric values directly into these fields, but it's more likely that you'll want to reference individual cells from the spreadsheet.

3. Click and drag the top of the dialog box to move it so that the spreadsheet underneath is at least partially visible.

4. Click the cursor in the first field of the dialog box, and then click the first cell you want to include in the calculation. This will automatically enter that cell reference into the field.

5. Click the next field in the dialog box, and then click the next cell you wish to include.

6. Repeat step 5 until all the fields you wish to include have been added to the fields in the dialog box (up to 30 can be selected).

7. Once you've finished, click the Okay button. Calc will insert the formula into the cell you selected at the start, showing the result of the formula.

After you've added a formula with the AutoPilot, you can edit it manually by clicking it and overtyping its contents. Alternatively, you can use the AutoPilot once again, by clicking the button on the Function bar.

Summing Figures

To add the values of a number of cells, you could use the AutoPilot and select the Sum function, as shown in Figure 25-2. The procedure for choosing the cells is the same as described in the previous section.

Figure 25-2. Creating formulas is easy using the AutoPilot wizard.

However, Calc provides a far easier method of creating the sum formula. Simply click the Sum icon (the Greek S character) on the Formula bar, and then select the cells you wish to include in the sum.

Tip 

You can select more than one cell by holding down the Ctrl key. You can select a range of cells in succession by clicking and dragging the mouse while pressing the Shift key.