Microsoft Office Tutorials and References
In Depth Information
Finding Where It Is
✓ The number of columns to offset: This can be a positive or negative
number. Use 0 for no column offset.
✓ The number of rows in the returned range: The default is the number
of rows in the reference range (the first argument).
✓ The number of columns to return: The default is the number of
columns in the reference range.
If you omit the last two arguments, then OFFSET returns a reference to a
single cell. If you include a value greater than 1 for either or both, then the
function’s return references a range of the specified size with the top-left cell
at the specified offset.
Figure 14-9 shows some examples of using OFFSET. Columns A through C
contain a ranking of the states in the United States by size in square miles.
Column E shows how OFFSET has returned different values from cells that
are offset from cell A3. Some highlights follow:
✓ Cell E4 returns the value of cell A3 because both the row and column
offset is set to 0: =OFFSET(A3,0,0).
✓ Cell E7 returns the value you find in cell A1 (the value also is A1). This
is because the row offset is –2. From the perspective of A3, minus two
rows is row number 1: =OFFSET(A3,-2,0).
✓ Cell E8 displays an error because OFFSET is attempting to reference a
column that is less than the first column: =OFFSET(A3,0,-2).
✓ Cell E10 makes use of the two optional OFFSET arguments to tell the SUM
function to calculate the sum of the range C4:C53: =SUM(OFFSET