Numbers '09: Adding and editing formulas using the formula bar
The formula bar, located beneath the format bar, lets you create and modify formulas in a selected cell.
Here are ways to work with the formula bar:
To add or edit a formula, select the cell and add or change formula elements in the formula bar.
To add elements to your formula, do the following:
- To add an operator or a constant, place the insertion point in the formula bar and type. You can use the arrow keys to move the insertion point around. When your formula requires an operator and you haven’t added one, the + operator is inserted automatically. Select the + operator and type a different operator if needed.
- Arithmetic operators perform arithmetic operations, such as addition and subtraction, and return numerical results.
- Comparison operators compare two values and return TRUE or FALSE.
- To increase or decrease the display size of formula elements in the formula bar, choose an option from the Formula Text Size pop-up menu above the formula bar.
- To increase or decrease the height of the formula bar, drag the resize control at the bottom right of the formula bar down or up, or double-click the resize control to auto-fit the formula.
- To remove an element from the formula, select the element and press Delete.
- To save changes, press Return, press Enter, or click the Accept button above the formula bar. You can also click outside the formula bar.
- To avoid saving any changes you made, click the Cancel button above the formula bar.
Adding references to the formulas
You use cell references to identify cells whose values you want to use in formulas. The cells can be in the same table as the formula cell, or they can be in another table on the same or a different sheet.
Cell references have different formats, depending on such factors as whether the cell’s table has headers, whether you want to refer to a single cell or a range of cells, and so on.
Here’s a summary of the formats that you can use for cell references.
|TO REFER TO||USE THIS FORMAT||EXAMPLE|
|Any cell in the table containing the formula||The reference tab letter followed by the reference tab number for the cell||C55 refers to the fifty-fifth row in the third column.|
|A cell in a table that has a header row and a header column||The column name followed by the row name||2006 Revenue refers to a cell whose header row contains 2006 and header column contains Revenue.|
|A cell in a table that has multiple header rows or columns||The name of the header whose columns or rows you want to refer to||If 2006 is a header that spans two columns (Revenue and Expenses), 2006 refers to all the cells in the Revenue and Expenses columns.|
|A range of cells||A colon (:) between the first and last cell in the range, using reference tab notation to identify the cells||B2:B5 refers to four cells in the second column.|
|All the cells in a row||The row name or row-number:row-number||1:1 refers to all the cells in the first row.|
|All the cells in a column||The column letter or name||C refers to all the cells in the third column.|
|All the cells in a range of rows||A colon (:) between the row number or name of the first and last row in the range||2:6 refers to all the cells in five rows.|
|All the cells in a range of columns||A colon (:) between the column letter or name of the first and last column in the range||B:C refers to all the cells in the second and third columns.|
|A cell in another table on the same sheet||Table name followed by two colons (::) and then the cell identifier||Table 2::B5 refers to cell B5 in a table named Table 2. Table 2::2006 Class Enrollment refers to a cell by name.|
|A cell in a table on another sheet||Sheet name followed by two colons (::), the table name if the cell name isn’t unique in the spreadsheet, two more colons, then the cell identifier||Sheet 2::Table 2::2006 Class Enrollment refers to a cell in a table named Table 2 on a sheet named Sheet 2.|
You can omit a table or sheet name if the cell or cells referenced have names unique in the spreadsheet.
When you reference a cell in a multi-row or multi-column header, you’ll notice the following behavior:
- The name in the header cell closest to the cell referring to it is used. For example, if a table has two header rows, and B1 contains “Dog” and B2 contains “Cat,” when you save a formula that uses “Dog,” “Cat” is saved instead.
- However, if “Cat” appears in another header cell in the spreadsheet, “Dog” is retained.
Adding functions to formulas
A function is a predefined, named operation (such as SUM and AVERAGE) that you can use to perform a calculation. A function can be one of several elements in a formula, or it can be the only element in a formula.
There are several categories of functions, ranging from financial functions that calculate interest rates, investment values, and other information to statistical functions that calculate averages, probabilities, standard deviations, and so on.
Although you can type a function into the text field of the Formula Editor or into the formula bar, the Function Browser offers a convenient way to add a function to a formula.
Left pane: Lists categories of functions. Select a category to view functions in that category. Most categories represent families of related functions. The All category lists all the functions in alphabetical order. The Recent category lists the ten functions most recently inserted using the Function Browser.
Right pane: Lists individual functions. Select a function to view information about it and to optionally add it to a formula.
Lower pane: Displays detailed information about the selected function.
To use the Function Browser to add a function:
- In the Formula Editor or the formula bar, place the insertion point where you want the function added.
Note: When your formula requires an operator before or after a function and you haven’t added one, the + operator is inserted automatically. Select the + operator and type a different operator if needed.
- Open the Function Browser by doing one of the following:
- Click in the formula bar and then click the Function Browser button.
- Click the Function button in the toolbar and choose Show Function Browser from the pop-up menu.
- Choose Insert > Function > Show Function Browser.
- Choose View > Show Function Browser.
- Select a function category.
- Choose a function by double-clicking it or by selecting it and clicking Insert Function.
- In the Formula Editor or the formula bar, replace each argument placeholder in the inserted function with a value.
To review a brief description of an argument’s value, hold the pointer over the argument placeholder. You can also refer to information about the argument in the Function Browser window.
To specify a value to replace an argument placeholder that has a disclosure triangle, click the disclosure triangle and then choose a value from the pop-up menu. To review information about a value in the pop-up menu, hold the pointer over the value. To review help for the function, select Function Help.
To specify a value to replace any argument placeholder, click the argument placeholder and type a constant or insert a cell reference. If the argument placeholder is light gray, providing a value is optional.