In this tutorial, you will learn how to use the offset function.
The OFFSET is a function that would return the range of cells, using the specified numbers of both columns and rows along with the beginning of the range, as it has been specified by the users. This is because the users have the ability to specify a size of range that would be returned, which gives the user a strong opportunity to decide the range.
Basics of 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.
These parameters are as specified by Microsoft Excel, and are reference, followed by rows, column, and the height and finally the width. Syntax of the offset function is:
OFFSET (reference, rows, cols, height, width)
- Reference: This is essential and required by the Microsoft Excel, as it is the initial range that needs to be offset. It is possible for it to be either one cells or more cells, depending on the desire of its users
- Rows: This is critical to the function and is the number of rows right from the beginning of that range that has been initially supplied to ace as beginning of range that would be returned.
- Cols (Columns): This is required by the function and act as the quantity of columns from the beginning, usually upper left of a range that has initially be supplied to a beginning of range that has returned.
- Height [height]: It is optional for the user, and would specify height of range that has returned. If this has been omitted, then it would automatically be returning a range at exactly the same range it has been supplied.
- Width [Width]: This is the last and optional argument that the offset function contains, and it solely specify width of a range that has returned. If this is omitted, then a range of returned value would automatically be the one the user supplied.
How does the OFFSET function work?
The OFFSET function works by first calculating the absolute address of the cell based on the reference cell, the number of rows to offset, and the number of columns to offset. If the optional argument is TRUE, the offset is relative to the current cell.
OFFSET 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
