OFFSET FUNCTION is one of the very useful functions in Excel.
It is found under the STATISTICAL GROUP of the formulas in MICROSOFT EXCEL.
OFFSET FUNCTION returns the reference to a cell or range which is at a fixed rows and columns from the cell.
OFFSET FUNCTION is yet another very useful function if we need to find out the values at a particular offset from the current cell, hence the name OFFSET.
This function is also used a lot in VBA when we need to work relatively.
PURPOSE OF OFFSET FUNCTION IN EXCEL
OFFSET FUNCTION returns the reference of the cell or range specified at a specified offset/cells away from the current cell.
PREREQUISITES TO LEARN OFFSET FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the STATISTICAL terms is an advantage for the use of such formulas.
- Of course, Excel software.
SYNTAX: OFFSET FUNCTION
The Syntax for the function is
=OFFSET ( REFERENCE , ROWS , COLUMNS, HEIGHT, WIDTH )
Reference is the reference from which we’d try to find the offset. It is the pivot of the function relative to which we’d find out the new reference.
Rows is the number of rows you want to offset from the reference. A positive number tells that these are below the reference whereas negative number tells that these are above the reference.
Cols is the number of columns we want to offset from the reference. It is again just like the graph. cols value 2 means 2 columns to the right and -2 means , 2 columns to the left.
Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.
EXAMPLE:OFFSET FUNCTION IN EXCEL
Let us take a random data to find out the offset values from a reference to understand its working.
|DATA FOR OFFSET EXAMPLE|
We’ll find out value of the cell 2,2 distance away from G18 and in second step we would try to find out the sum of H19:I20 using the OFFSET FUNCTION
STEPS TO USE SMALL FUNCTION-EXAMPLE
1. Place the cursor in the cell and type the following function
2. USE THE FOLLOWING FUNCTION
=OFFSET(G18,2,2,1,1) FOR FINDING THE 2,2 VALUE OF G18.
First argument is the reference cell, second is the row which is 2 row below, third is the column , which is two columns to the right, shown by the blue colour, The height and width be one, one so the intersection of row and column shown by the RED COLOUR is our answer.
3. Again place the cursor in other cell and enter the following function
=SUM(OFFSET(G18,1,1,2,2)) FOR FINDING THE SUM OF H19:I20.
In this example, first argument is the reference, second and third are the offset 1,1 which will take us to the value H19. After this the height and width be 2,2 will make it a range of 4 cells. The outer function SUM will sum all the contents and results will be shown.
The answers can be checked manually. We find that all answers are correct.
generalized STEPS TO USE COUNTA function
- USE THE FUNCTION
- =OFFSET( reference, row, column, height, width )
- Press ENTER and the result will appear.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.