CODEWITHSUNDEEP
excelvba
Alex Gordon
Alex Gordon

Reputation: 60912

what is the purpose of OFFSET in excel?

In Excel, the Offset function returns a reference to a range that is offset a number of rows and columns from another range or cell.

can someone please tell me what that means?

for example in this formula:

=OFFSET($B$4,ROW()-ROW($F$4),0,1,1)

what is it doing?

Upvotes: 0

Views: 1273

Answers (2)

Ross
Ross

Reputation: 300

That example formula is odd, because it references a cell to get the row then takes that away from the current row - this bit ROW()-ROW($F$4, which might as well be row()-4, but there you go.

Offset, works like this

A cell location to start at, how many rows away from this, cols away from the this, optional size(rows), optional size(cols).

Upvotes: 1

avpaderno
avpaderno

Reputation: 29748

The purpose of OFFSET(BASE, ROW-OFFSET, COLUMN-OFFSET, NUM-ROWS, NUM-COLUMNS) is to select the content of cells that are NUM-ROWS rows and NUM-COLUMS distant from the base cell. If the selected cells are just one, then the content of that cell will be used as result, otherwise the result will be an array that can be passed to functions as SUM.

In your example, the function is simply selecting the content of the cell B4.

Upvotes: 2

Related Questions