OFFSET function
OFFSET returns the value of a cell the location of which is offset by a certain number of rows and columns.
Syntax
OFFSET(reference,rows,cols,height, width)
- reference is the cell from which the function searches for the new reference.
- rows is the number of cells by which the reference was corrected up (negative value) or down.
- cols is the number of columns by which the reference was corrected to the left (negative value) or to the right.
- height (optional) is the vertical height for an area that starts at the new reference position.
- width (optional) is the horizontal width for an area that starts at the new reference position.
Example
=OFFSET(A1,2,2) returns the value in cell C3 (A1 moved by two rows down and two columns right). If C3 contains the value 100, this function returns the value 100.
=SUM(OFFSET(A1,2,2,5,6)) determines the total of the area that starts in cell C3. This area has a height of five rows and a width of six columns (area=C3:H7).