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
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.