Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours |
![]() |
|||||||||||||
Hour 15: Using Formulas, Functions, and Names |
![]() |
|||||||||||||
|
|
Spreadsheets also provide a set of tools that make calculations a lot easier. These are called functions. A function takes a cell or a set of cells as a parameter, and does some calculation on those cells to produce a value.
The value produced by a function can be combined with other functions to reach a final cell value. All the calculation is done instantly by StarOffice.
For example, look at the column of sales numbers and the sample formula that you just entered. The formula you entered is shown again here:
=B6+B7+B8+B9+B10
This works fine, but it's not ideal. What if you had a list of 50 numbers to add? Instead of listing all the cell references, you can use a StarOffice function called sum(), which adds together all the cells referred to as parameters of the function. That formula looks like the following:
=sum(B6:B10)
Using either formula, changing one of the values in cells B6 through B10 changes the total shown in cell B11. The recalculation is automatic. Using the sum() function, however, has advantages that will become apparent--advantages beyond being easier to type in.
Following is another example to show how useful functions can be.
Suppose you have prepared a spreadsheet of sales figures. Each time you open the spreadsheet for review and printing, you want it to contain a date and time stamp at the top of the spreadsheet. This indicates when that sheet was updated and printed.
You can enter a new date and timestamp each time you open the spreadsheet, or you can place the following formula in a cell somewhere in the spreadsheet:
=now()
Now, each time you open or recalculate the spreadsheet, the now() function inserts the current date and time into that cell (see Figure 15.3).
Figure
15.3
The
now()
function can
be used to insert the current date and time in a cell each time you open or
recalculate the spreadsheet.
When you begin using StarOffice to create complex spreadsheets, you might not know which functions are available to get the results you want. The sum() function is probably the most used function, but beyond that, how do you find and use the right function?
The answer is the StarOffice Function AutoPilot. By using this tool, you can review a list of functions by category, choose the one that you think fits your needs, and then review what the function does and be guided to enter the correct parameters to make it work correctly.
For example, suppose you are working with the list of sales figures that you entered previously. You want to know some information beyond the sum of the column of figures.
If you want to know the average daily sales (assuming each cell was a day's sales), for example, you can use the following procedure to find and use the correct formula:
1. Click on cell A12 and type Daily Average.
2. Click on cell B12 (where you want the Average figure stored).
3. Choose Function from the Insert menu. The Function AutoPilot dialog box appears (see Figure 15.4).
Figure
15.4
The Function AutoPilot dialog box helps you choose and
then enter parameters for any StarOffice function.
4. Select a category for the function that you want to use. You won't always know immediately, but assume that the averaging functions are part of the statistical category.
With Statistical selected in the Category list, only the statistical functions are displayed in the Function list.
Tip - If the cell with focus was empty when you launched the Function AutoPilot, a description appears to the right of the Function list when you click on a Function name (see Figure 15.5). |
Figure
15.5
The Function AutoPilot shows a description of any function that you select.
5. Sometimes you'll need to review several descriptions to see which function best fits your needs. This time the answer is obvious: Click on the AVERAGE function.
6. With AVERAGE selected in the Function list, click on the Next>> button. The parameter entry dialog box for the AVERAGE function appears (see Figure 15.6).
Figure
15.6
The Function AutoPilot guides you in entering all the parameters needed to make
the selected function work correctly.
Tip - Notice that the description in the dialog box above the field names includes information about the current parameter field. When you move to the next parameter field, the description is updated to tell you about it. |
7. You want to calculate the average of a single range of numbers. In the first field, number1, type
b6:b10
Tip - Notice that the Result field shows you what the result will be as you enter the cell range in the number1 field. Refer to this area to see that the results you're programming with the selected function don't leave common sense behind. |
8. Because you don't have any other numbers to add to this average, press OK to close this dialog box and insert the completed function into the spreadsheet at the current cell location.
Each of the functions listed in the Function AutoPilot dialog box includes the descriptive information and parameter fields to help you use the functions correctly.
You already learned how you can press F2 or double-click on a cell to edit the contents of that cell, including cells that contain a formula you've entered.
The Function AutoPilot has a partner dialog box called the Edit Function dialog box that can help you edit any function.
Suppose that you entered the AVERAGE function example described previously, and now you need to update the cell references to include a second block of cells.
If you've forgotten the format of the function parameters, you can click on the cell containing the AVERAGE() function, and then choose Function from the Insert menu.
StarOffice detects that the cell already contains a function. The Function AutoPilot dialog box is opened and the function is displayed, as in Figure 15.7.
Figure
15.7
When started in a cell containing a function, the Function AutoPilot dialog
box helps you update a function that you've already entered.
The cell references that were part of the selected formula are contained in the first field. You can enter other cell references and read the helpful description of how the function is used.
When you're finished, choose OK to close the dialog box with the updated function.
StarOffice provides another handy way to enter function information, instead of typing in cell references or using the Function AutoPilot dialog box. You can also use the mouse to drag across an area of cells that you want to use in a function.
Using the mouse can be confusing at first, but it's a very convenient feature after you're comfortable with it. The following example uses the same list of sales figures that you worked on for the previous examples, but erase the sum and average formulas that you entered previously before trying this example:
1. Click on cell B11, where the column total is displayed.
2. Type this much of the sum() function in the cell, but don't press Enter:
=sum(
3. Click and hold the mouse button on the bottom cell in the list of sales figures (B10).
4. Drag the mouse pointer up to the top of the list of sales figures (cell B6). Notice that the block of cells is outlined in red, and that the cell range reference appears in the sum() function shown in cell B11 (see Figure 15.8).
Figure
15.8
You can use the mouse to drag around a block of cells that you want to use as
a parameter in a function.
5. Release the mouse button and end the operation by pressing ) to close the sum() function.
6. This formula is finished, so press Enter. The cell value is calculated. You can add other things to this formula instead of pressing Enter if you need to.
Caution - You can also use the keyboard arrows with Shift to select a block of cells while entering a formula, but it's a little more confusing than using the mouse. |
You can use this mouse-dragging technique for any function that uses a cell reference as a parameter, or even for regular math operations in a formula. You can either click on a cell to enter a single cell reference, or click and drag to enter a range of cells.
When you enter formulas in your spreadsheet, the values that are calculated from those formulas can change continually as you enter new information in the other cells of the spreadsheet.
StarOffice automatically recalculates everything in your spreadsheet as you enter new or revised numbers in cells. StarOffice determines which cells need to be recalculated by watching which cells you enter numbers in.
If you've entered a large spreadsheet, however, with many complex formulas, you might find that the constant recalculation slows down your work with StarOffice. This is especially true if you use a lot of financial or mathematical functions with large numbers of cells.
You can turn the auto-calculate feature off so that StarOffice waits for explicit instructions before recalculating the spreadsheet.
To turn off the auto-calculate, select Cell Contents from the Tools menu, and then choose AutoCalculate from the Cell Contents submenu. This feature is unchecked, and the spreadsheet is no longer recalculated until you explicitly request a recalculation.
With AutoCalculate turned off, you can give the command to recalculate by choosing Recalculate from the Cell Contents submenu (under the Tools menu).
Tip - Press F9 at any time to recalculate the spreadsheet. |
Sams Teach Yourself StarOffice® 5 for Linux in 24 Hours |
![]() |
|||||||||||||
Hour 15: Using Formulas, Functions, and Names |
![]() |
|||||||||||||
|
© Copyright Macmillan USA. All rights reserved.