PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

EXCEL FUNCTIONS-OFFSET

INTRODUCTION

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.
Helpful links for the prerequisites mentioned above

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

DATA SAMPLE

Let us take a random data to find out the offset values from a reference to understand its working.

GYANKOSH.NET    
DATA FOR OFFSET EXAMPLE   
      
      
245137917984403459945793
678268147708520021796761
872752876720444879907595
374021905898717794836428
583636896864816369928722
583880863278328624747236
578174327718724961275702
879077936887456977963917
668278448956942858247605
992159038091458012374662
263054171561364078327935
688376608678230342269424
741738171561486212776254
865952071528488055594299
449394639160960333693228
717160559598826348509647
179410359376437116863851
569911515791267188302901

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

GYANKOSH OFFSET FUNCTION USAGE IN EXCEL
USING OFFSET FUNCTION IN EXCEL

STEPS TO USE SMALL FUNCTION-EXAMPLE

STEPS:

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

HERE ARE THE STEPS TO USE COUNT FUNCTION
     PLACE YOUR CURSOR IN THE CELL WHERE YOU WANT THE RESULT.
  • 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

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]