Dear Reader,
Putting down few thoughts on the Offset function in excel. This function is highly useful in creating a dynamic model. But many a time due to complexity in the application it remains a bit of mystery to users. An attempt to help you understand this function with a practical application of the same.
Offset function is used to fetch the content of a cell whose exact location is not known to us but its location relative to a known "Reference Cell" is known to us.
Observe the below snapshot
We are interested in fetching the content "abcd" and we know that it lies 2 rows below and on the same column with respect to the cell "Ref". The syntax for an offset formula is as shown below
OFFSET(reference,rows, cols,[height],[width])
To fetch abcd, the offset formula would look like OFFSET(M6,2,0). Please observe for the row input we have use a positive to 2, which means that the positive input in the row argument of an offset function would search in the downwards direction and a negative input vice-versa. In another example if we are required to fetch "lmn". The formula would change to OFFSET(M6,-3,-2) reflecting that the required cell is above the reference cell and to the left due to negative input to the column argument.