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.