Hello Friends,
The "Offset" function in excel is a powerful function. The Offset function returns a reference to a range that is given a number of rows and columns from a given reference. It is a function that returns the value of cell or range of cells which is specified by a number of rows and columns from the cell and range of cells.
Syntax of the Offset Function.
=OFFSET (reference, rows, cols, [height], [width])
Reference: Reference of cell or range of cells from where a row and column count will start.
Rows: Count the number of rows below (a positive value), above (a negative value), same row (0).
Column: Count the number of column right (a positive value), left (a negative value).
Height: It is optional. The height in rows of the returned reference.
Width: It is optional. The Width in a column of the returned reference.
Example of the OFFSET function to understand it.
In this example, I have taken monthly sales of 3 items of a computer store. Now I want to calculate the sum of the sales of all three items in the month of July.
I used a formula
A3 is the starting point from where we can calculate the number of rows and columns. The reference of the "January" value of A3 is 0 row. and 0 columns.
OFFSET(A3,6,1) returns the reference of the cell which is located at 6 rows below and 1 column right from the cell A3.
It is a very powerful function and widely used in a complex function. Here some more examples of OFFSET function.
Sum Of every 3 cells
The "Offset" function in excel is a powerful function. The Offset function returns a reference to a range that is given a number of rows and columns from a given reference. It is a function that returns the value of cell or range of cells which is specified by a number of rows and columns from the cell and range of cells.
Syntax of the Offset Function.
=OFFSET (reference, rows, cols, [height], [width])
Reference: Reference of cell or range of cells from where a row and column count will start.
Rows: Count the number of rows below (a positive value), above (a negative value), same row (0).
Column: Count the number of column right (a positive value), left (a negative value).
Height: It is optional. The height in rows of the returned reference.
Width: It is optional. The Width in a column of the returned reference.
Example of the OFFSET function to understand it.
In this example, I have taken monthly sales of 3 items of a computer store. Now I want to calculate the sum of the sales of all three items in the month of July.
I used a formula
=SUM(OFFSET(A3,6,1),OFFSET(A3,6,2),OFFSET(A3,6,3))
In the above formula, I have used the OFFSET function 3 times for the references of three different items.A3 is the starting point from where we can calculate the number of rows and columns. The reference of the "January" value of A3 is 0 row. and 0 columns.
OFFSET(A3,6,1) returns the reference of the cell which is located at 6 rows below and 1 column right from the cell A3.
It is a very powerful function and widely used in a complex function. Here some more examples of OFFSET function.
Sum Of every 3 cells
No comments:
Post a Comment