Site icon Best Excel Tutorial

How to Use OFFSET Function in Excel

A Simple OFFSET Usage

A Simple OFFSET Usage

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)

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

Exit mobile version