Apple iWork '09 User Manual
Page 220
Â
column-offset: The number of columns from the base cell to the target cell. column-
offset is a number value. 0 means the target cell is in the same column as the base
cell. A negative number means the target is in a column to the left of the base.
Â
rows: An optional value specifying the number of rows to return starting with the
offset location.rows is a number value.
Â
columns: An optional value specifying the number of columns to return starting
with the offset location.columns is a number value.
Usage Notes
OFFSET can return an array for use with another function. For example, assume you
Â
have entered into A1, A2, and A3, the base cell, the number of rows, and the number
of columns, respectively, that you wish to have summed. The sum could be found
using =SUM(OFFSET(INDIRECT(A1),0,0,A2,A3)).
Examples
=OFFSET(A1, 5, 5) returns the value in cell F6, the cell five columns to the right and five rows below
cell A1.
=OFFSET(G33, 0, -1) returns the value in the cell to the left of G33, the value in F33.
=SUM(OFFSET(A7, 2, 3, 5, 5)) returns the sum of the values in cells D9 through H13, the five rows and
five columns that begin two rows to the right of and three columns below cell A7.
Assume that you have entered 1 in cell D7, 2 in cell D8, 3 in cell D9, 4 in cell E7, 5 in cell E8, and 6 in
cell E9.
=OFFSET(D7,0,0,3,1) entered in cell B6 returns an error, since the 3 rows and 1 column returned (the
range D7:D9) does not have one single intersection with B6 (it has none).
=OFFSET(D7,0,0,3,1) entered in cell D4 returns an error, since the 3 rows and 1 column returned (the
range D7:D9) does not have one single intersection with B6 (it has three).
=OFFSET(D7,0,0,3,1) entered in cell B8 returns 2, since the 3 rows and 1 column returned (the range
D7:D9) has one single intersection with B8 (cell D8, which contains 2).
=OFFSET(D7:D9,0,1,3,1) entered in cell B7 returns 4, since the 3 rows and 1 column returned (the
range E7:E9) has one single intersection with B7 (cell E7, which contains 4).
Related Topics
For related functions and additional information, see:
“Listing of Reference Functions” on page 206
“Value Types” on page 36
“The Elements of Formulas” on page 15
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26
“Pasting from Examples in Help” on page 41
220
Chapter 9
Reference Functions