Returns value from a data as per given offset range.
The Excel OFFSET function returns one or multiple data points of a data set by providing a reference point of data set with a specified number of rows and columns to offset.
OFFSET is another function that can club with other functions as part of a more complicated formula.
=OFFSET(reference, rows, cols, [height], [width])
reference – A cell address or range address but not named ranges.
row – Number of positive or negative rows to offset
cols – Number of positive or negative column to offset
height [optional] – Value of number of rows to return.
width [optional] – Value of number of columns to return.
Cell reference based on the arguments
The OFFSET function returns a specified data point that is offset by a certain number of rows and columns from a reference cell.
The formula = SUM((OFFSET(B4,2,1,1,4)) states B4 as a starting point (reference) move 2 rows below to B6 (rows), 1 column right (cols) to C6 and 1 row [height] do not change position and cover 4 rows below i.e. C6:F6 [width]. So, the return value is summing all number in C6:F6.
If you keep [height] and [width] empty the OFFSET return an individual cell value and for [height] and [width] considered same as cell range (reference).
The Offset function can also be used as an array formula.