How to use OFFSET function in excel?

OFFSET function returns a reference to a range or cell, when we give offset a number of rows and columns from another range or cell. Number of row and column can be negative and positive.

how to use offset function in excel charts

OFFSET very useful function in Excel. We use offset function for creating a rectangular range in Excel.

Inbuilt Function

OFFSET function is an inbuilt function in Microsoft Excel which is categorized as LOOKUP & REFRENCES Function.

Syntax There are five arguments in OFFSET Function

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

how to use offset function in excel

(A). Reference – This is starting point (Range or Cells), where we apply offset. This is mandatory arguments for OFFSET.

(B). Rows – The number of Rows applied as offset to the range or cell. We can give number of rows as negative and positive. This is mandatory arguments for OFFSET.

(C). Cols – The number of columns applied as offset to the range or cell. We can give numbers of column as negative and positive. This is mandatory arguments for OFFSET.

(D). Height – Height is the number of rows. This is optional arguments.

(E). Width – Width is the number of columns. This is optional arguments.

 

How to use OFFSET function in excel?

OFFSET Examples

Example – 1

offset function example

In this example, How to change direction of Range value from OFFSET.

We use this formula =OFFSET($A$1,COUNTA(A:A)-ROW(A1),0) for solve this question

(A). Reference – $A$1 is Reference

(B). Rows – We find Row offset from COUNTA(A:A)-ROW(A1).

(C). Cols – Column offset is 0.

After that OFFSET Function reverse the value direction in cells.

 

How to use OFFSET function in excel?

Example – 2

how to use offset function in excel with example

In this example, How to find each value from OFFSET.

We use this formula =OFFSET($A$1,MATCH(D2,$A$2:$A$16,0),1) for solve this question

(A). Reference – $A$1 is Reference

(B). Rows – We find Row offset from MATCH(D2,$A$2:$A$16,0). MATCH function returns the row number of each lookup value.

(C). Cols – Column offset is 1.

 

How to use OFFSET function in excel?

Example – 3

offset formula in excel

In this example, We find average of all last 3 month from OFFSET.

We use AVERAGE, COUNTA and OFFFSET function and create this formula =AVERAGE(OFFSET(A2,0,COUNTA(B2:M2)-2,1,3)) for solve this example.

(A). Reference – $A2 is Reference.

(B). Rows – Number of Row offset is 0.

(C). Cols – We find Column offset from COUNTA(B2:M2)-2.

(D). Height – Height is 1 because we have evaluate average for only one row.

(D). Width – Width is 3 because we have to evaluate average for last three month.

 

After that AVERAGE function Returns average of number which is evaluated by OFFSET Function.

 

How to use OFFSET function in excel?

Example – 4

offset with sum function

In this example, We have to add Sum of all last 3 month from OFFSET.

We use SUM, COUNTA and OFFFSET function and create this formula =SUM(OFFSET(A2,0,COUNTA(B2:M2)-2,1,3)) for solve this example.

(A). Reference – $A2 is Reference.

(B). Rows – Number of Row offset is 0.

(C). Cols – We find Column offset from COUNTA(B2:M2)-2.

(D). Height – Height is 1 because we have to add number for only one row.

(D). Width – Width is 3 because we have to add number for last three month.

After that SUM function add number which is evaluated by OFFSET Function.

Download Workbook

How to use Vlookup in excel with example?

 

Leave a Reply

Your email address will not be published. Required fields are marked *