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

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

What Excel does? How to use formula in Excel?

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


2451	3791	7984	4034	5994	5793
6782	6814	7708	5200	2179	6761
8727	5287	6720	4448	7990	7595
3740	2190	5898	7177	9483	6428
5836	3689	6864	8163	6992	8722
5838	8086	3278	3286	2474	7236
5781	7432	7718	7249	6127	5702
8790	7793	6887	4569	7796	3917
6682	7844	8956	9428	5824	7605
9921	5903	8091	4580	1237	4662
2630	5417	1561	3640	7832	7935
6883	7660	8678	2303	4226	9424
7417	3817	1561	4862	1277	6254
8659	5207	1528	4880	5559	4299
4493	9463	9160	9603	3369	3228
7171	6055	9598	8263	4850	9647
1794	1035	9376	4371	1686	3851
5699	1151	5791	2671	8830	2901

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

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.

CONTENTS