Using the Excel OFFSET Function, with Examples
——————————————————————————————————————————
OFFSET Function:The excel OFFSET function returns a range, which is offset by a specified number of rows and columns from a reference range.Syntax: OFFSET(reference_range, rows, columns, height, width)1. reference_range is a single cell or a range of adjacent cells from which the offset is based. It is the starting reference to which the offset is applied to return a range.
2. rows is the number of rows used to offset from the reference_range. A positive number of say 3 means the returned range (upper-left cell) will be 3 rows below the reference_range, and a negative number of say 2 means that the returned range (upper-left cell) will be 2 rows above the reference_range.
3. columns is the number of columns used to offset from the reference_range. A positive number of say 3 means the returned range (upper-left cell) will be 3 columns to the right of the reference_range, and a negative number of say 2 means that the returned range (upper-left cell) will be 2 columns to the left of the reference_range.
4. height (optional argument) is the number of rows of the returned range. If omitted, the returned range will be of the same height as reference_range.
5. width (optional argument) is the number of columns of the returned range. If omitted, the returned range will be of the same width as reference_range.
6. The function will return an error value of #REF! if the offset returns a range which is beyond the worksheet range.
——————————————————————————————————————————
Example 1 – Basic OFFSET Function:Table 1=OFFSET(C2,2,3,1,1) [Formula]Calculating the reference: with reference to cell C2, two rows down (ie. row 4) and three columns to the right (ie. column F), with height and width being the same (ie. single cell).Displays the value in cell F4 viz. 39. (Refer Table 1)
—————————————————————————————————————————-
Example 2 – Using OFFSET function within a SUM function:Table 2=SUM(OFFSET(C2:E4, -1, 0,3, 3)) [Formula]What does OFFSET part of formula return: with reference to range C2:E4, one row up (ie. row 1) and same column (ie. column E), with 3 rows in height and 3 column width ie. returns range C1:E3.Sums the range C1:E3 and returns 160. (cell E9 in Table 2)
—————————————————————————————————————————
Example 3 – Reference Out of Range in OFFSET Function:=OFFSET(C2:E4, 0,-3, 3,3) [Formula]Calculating the reference: with reference to range C2:E4, same row (ie. row 2) and three columns to the left (ie. out of range because there cannot be 3 columns left of column C), with 3 rows in height and 3 column width.Returns an error, because the reference is not on the worksheet (#REF!).
—————————————————————————————————————————
Example 4 – Reference to a Cell Range:Table 3=OFFSET(A1, 2,3, 4,3) will return a ‘4 row by 3 column’ cell range starting from D3 (A+3 columns, 1+2 rows =D3) which is: D3: F6. (Refer Table 3)The Offset function returns a cell range (of multiple cells) and so the result must either be entered into a range of cells (C9:E12) as an Array Formula (Refer Table 3), or must be supplied to a further Excel formula that uses an array as an argument {Refer Example 2 above}.
—————————————————————————————————————————
Example 5 – Reference to a Named Range and Using a Cell Reference:Table 4a=Offset(Data, 0,B5) [Formula]Will offset the named Range ‘Data’ (A1:A4) by 5 (value in cell B5) Columns (Refer Table 4a).Table 4bFormula will return the range ‘F1:F4’. Entered Array Formula in range E9 :E12 which returns the range F1:F4 – (Refer Table 4b).
