Lesson#105: Excel function OFFSET and some examples

Here I will show some examples related to the function OFFSET. Before starting I will discuss what is this function and how it works. The name OFFSET itself gives a hint about the function. This function returns the result by making an offset from the original result with a number of columns or rows or both. The definition of this function is here,

Excel function OFFSET creates an offset to the original cell and returns the value from the offset cell. Positive offset in rows is downward, negative is upward, positive offset in columns is towards the right, and negative is toward left.

Syntax  OFFSET(reference, rows, cols, [height], [width])

reference  The original cell, input as a cell reference or range.

rows  The number of rows to offset below or above the reference.

cols  The number of columns to offset to the right or left of the reference.

height  [optional] The height in rows of the returned reference.

width  [optional] The width in columns of the returned reference.

So how it is useful so much? I will show you some examples here.

Here I am showing you an example. This is an example of the duty schedule of one person, Mr. A. A has been present on duty at the following time. I want to take out the time at the end of row (H column) when he was present.

So I developed a formula to return the value from the 1st row at that column where A is written. It’s like =OFFSET(A2,-(ROW(A2)-1),MATCH(“A”,B2:G2,0))

See how the formula returns the 1st-row values.

Now let me show another example for offset.

Here I am having a manpower deployment schedule. As per schedule all person has been deployed from Jan-18 but ended at different months. I will take out the month from the schedule on which deployment ends.

So I have developed this formula =OFFSET(B2,-(ROW(B2)-1),COUNTA(C2:AI2))

This formula counts the number of characters in cells and makes an offset to return the ending month in every row.

So these are some examples of function OFFSET. I will discuss this useful function in my next posts.