How to use OFFSET Function | Microsoft Excel

How to use OFFSET Function | Microsoft Excel

Offset Function


The offset function is working on a reference cell and it will give you a result from the intersection of the column and row.

You should know about offset function if you really want to do fast and precise work on excel.

Syntax of OFFSET Function

=OFFSET(reference,rows,cols,[height],[width])

Arguments of OFFSET Function

Required
reference: Reference is a point from where a counting start for row and columns, it could be a cell or a range.
rows: it is a row number in which you have to find your data. The counting of the row number starting from 0. where a reference cell count as 0
cols: it is a column number in which you have to find your data. Same as the row the counting of the column number starts from 0. where reference cell is count as 0

Optional

height: if you are counting for more that one row then you have to show how much rows participate in the process
width: if you are counting for more that one column then you have to show how much columns participate the processes.

Behavior of OFFSET Function


Here we have to find marks of social studies in the second exam.

we have used a simple formula
=OFFSET(A2,2,3)
Where 
A2 is a reference cell
2 is a row number, the numbering of the row is starting from a reference cell (reference cell will count as 0 after then cell number counted as 1,2,3…). The second row is the Second Exam
3 is a column number, The third column is a Social Studies
so output will be the intersection of the row number and column number which is 55
the value of the second exam in social studies is 55

Usage of OFFSET Function

The usage of the offset function is to find a specific value using row number and column number

Return of OFFSET Function

OFFSET function will return reference value from the intersection of the row and column.

Error values

Error Commands
Description
#VALUE
You have made mistake with reference cell or adjacent cell
#REF
Offset reference of rows and column are over the edge of worksheet

Leave a Reply

Your email address will not be published. Required fields are marked *