OFFSET function

In this tutorial you will learn how to use the offset function.

OFFSET is a function of Excel which allows you to select an area on the basis of selected parameters. OFFSET returns the field - a field of a sheet, or if you just pointed it yourself by mouse.

Syntax of the offset function is:

OFFSET (reference, rows, cols, height, width)

  • Reference - starting cell
  • Rows - number of rows to which you refer (positive - down and a negative number - up)
  • Cols - the number of columns, to which you refer (positive - to the right, and a negative number to the left of the starting cell)
  • Height - the height (number of rows) which the function refers
  • Width - the width (number of columns) which the function refers

 

Examples of OFFSET function's formulas: 

=OFFSET(A1:B2,1,1,1,1) - the formula returns the value of cell B2 

=OFFSET(A1,2,2) - returns the value in cell C3 (A1 moved by two rows and two columns down). If the cell C3 contains the value 100, the function returns a value of 100

=SUM(OFFSET(A1,2,2,5,6)) - is the total size of the area beginning in cell C3, which has a height of 5 rows and 6 columns (area =C3:H7)

This extract every fourth value =OFFSET($B$2,(ROW()-2)*4.0)

This sum sales for a specified period: =SUM(OFFSET($B$2,0,0,$D$2,1))

This extract every other column: =OFFSET($A2,0,(COLUMN()-1)*2)

=COUNT(OFFSET(A1,2,2,5,6)) - counts cells in C3:H7 area 

=AVERAGE(OFFSET(A1,2,2,5,6)) - calculates average in C3:H7 area

 

You will also need OFFSET function to create Dynamic Charts.