Formulas and Functions

Table Of Contents

Â column-oset: The number of columns from the base cell to the target cell. column-

oset 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

oset location.rows is a number value.

Â columns: An optional value specifying the number of columns to return starting

with the oset 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 ve columns to the right and ve 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 ve rows and

ve 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).