- Formula Basics
- Summarizing Data Quickly
- Elements of Formulas
- Entering Formulas
- Determining the Order of Operations
- Understanding Cell References
- Using Cell References
- Understanding Comparison Operators
- Using Comparison Operators
- Understanding Functions
- Using Functions
- Copying and Moving Formulas
- Handling Errors
Understanding Cell References
Formulas involving only constants are fine when you want to use Numbers as a jumped-up pocket calculator, but the real power of formulas comes from doing calculations with the raw data that are already in your spreadsheet. To do so, use cell references to identify (point to) cells whose values you want to use in formulas.
When you double-tap a formula cell or open the formula keyboard, the table’s reference tabs sprout letters and numbers that identify the columns and rows, respectively . Each cell reference is an address named for the column–row intersection where the cell is located. B2, for example, is the cell at the intersection of column B and row 2. A range of cells is identified by a pair of cell references separated by a colon (:). A1:B3, for example, refers to the rectangular block of six cells between A1 and B3 inclusive—that is, cells A1, A2, A3, B1, B2, and B3.
A cell reference in a formula tells Numbers to get that cell’s value and use it in the formula’s calculation. The simplest example is
=A1
which sets the value of the formula cell to whatever value is in cell A1. You can treat cell references like ordinary values. The formula
=A1 × 2
returns twice the value of A1, provided that A1 holds a number. shows a formula that references multiple cells.
Cells also are referenced by name by using header-column and header-row values (see “Rows and Columns” in Chapter 2). If cell B4 has the header-column value Test 1 and the header-row value S333, its named reference is Test 1 S333.
Referenced 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 whether they refer to a single cell or a range of cells, whether the cell’s table has headers, and so on. Table 4.4 lists the formats that Numbers uses for cell references.
TABle 4.4 Cell Reference Formats
Referemce |
Format |
Example |
A cell in the same table that contains the formula |
The cell’s reference tab letter (column) followed by its reference tab number (row) |
D3 refers to the third row in the fourth column. |
A range of cells |
A colon (:) between the first and last cell in the range, using reference tab notation |
D1:D4 refers to the first four cells in the fourth column. |
All the cells in a column |
The header-column name or the column letter |
D refers to all the cells in the fourth column. Test 2 refers to all the cells in the column whose header is Test 2. |
All cells in a range of columns |
A colon (:) between the header-column names or the letters of the first and last columns in the range |
C:D refers to all the cells in the third and fourth columns. Test 1:Test 4 refers to all the cells in the columns whose headers range from Test 1 to Test 4, inclusive. |
All the cells in a row |
The header-row name or row-number:row-number |
2:2 refers to all the cells in the second row. S444 refers to all the cells in the row whose header is S444. |
All cells in a range of rows |
A colon (:) between the header-row names or the row numbers of the first and last rows in the range |
1:5 refers to all the cells in the first five rows. S111:S444 refers to all the cells in the rows whose headers range from S111 to S444, inclusive. |
A cell in a table that has a row header and a column header |
The header-column name followed by the header-row name |
Test 2 S444 refers to a cell whose header row is S444 and whose header column is Test 2. |
A cell in another table on the same sheet |
The table name followed by two colons (::) and then the cell reference |
Table 2::C5 refers to cell C5 in the table named Table 2. Test Scores::Test 2 S444 refers to a cell by name. |
A cell in a table on another sheet |
The sheet name followed by two colons (::), the table name, two colons (::), and then the cell reference |
Sheet 1::Table 2::C5 refers to cell C5 in the table named Table 2 on a sheet named Sheet 1. Grades::Test Scores::Test 2 S444 refers to a cell by name. |