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