## Offset function in ExcelOffset() is an Excel function that is used to get the reference of a range and return back to the user. It is a bit tricky function and not so easy to understand and use. Once you learn and get it, you can use it on your Excel worksheet easily. ## Note: The resultant value can be a single cell or range of multiple adjacent cells.This chapter will brief you on the offset function functionality with its syntax and examples. You can find this LOOKUP function (user interface) under the ## Use of offset()Using the offset() function, you can directly move to a cell in any direction (left, right, up, down, as well as diagonally). For example, A rook can move only straight in left, right, up, and down, not in diagonal on a chessboard,. Similarly, using other functions of Excel, a user can straight go to a cell. The offset() function enables the users to move diagonally. ## SyntaxHere is the syntax of the offset() function. It accepts five arguments in it. Provide a starting point in reference parameter and number of rows and columns to move left, right, and up, down. While height and width are optional. ## Parameter listThe OFFSET() function have five parameters in which first three are mandatory, and others are optional parameter - ## Required arguments
## Optional arguments
Usually, this parameter is used to get the multiple values to be returned.
## Note: You can use the OFFSET() function inside another Excel function to handle the multiple values returned by it on using the height and width parameter.Now, we will illustrate the theory in practical and with the help of example, you will get it better. ## Return ValueThe OFFSET() function returns a reference to the range. This reference can be a single cell or multiple cells. ## OFFSET WarningThe OFFSET() function is a Excel offers a non-volatile function to return a reference, like ## OFFSET ExampleBefore start learning the OFFSET function directly on an Excel worksheet, try to understand it with the help of syntax examples. See some OFFSET syntax as the examples below:
## Need of Offset() functionThe offset() function is very helpful in the dynamic calculation. Let's understand using an example that why this function is needed. We have a problem that is resolved by offset().
We have a set of data of 7 days in which we want to calculate the average of the last 5 days. This can be easily done using the Average formula (=AVERAGE(B3:B7)) of Excel. But the problem is - we have added one more row (B8) in the end and again calculated the average of the last 5 days. In this scenario, we have to change the range of the targeted column, and the average formula will be (=AVERAGE(B4:B8)). The resultant value will also be changed now.
This problem can be resolved using the offset() function. It enables the users to get the reference of the cell and then calculate the SUM, AVERAGE, or other operation with it by selecting multiple values using its optional parameters. =AVERAGE(OFFSET(A2,5,3,2,1)) ## How OFFSET() function is used?With the help of different examples, you will now see how the OFFSET() function is used. For the example of the OFFSET() function, we have the following set of data of one month. We have this worksheet containing the weekly report of each day in one month. We will now apply the OFFSET() formula on this worksheet. ## Without height and width parameter## Example 1
=OFFSET(A2,4,3)
See that the 3100 has been returned on executing the OFFSET() function. In this example, we have only used the first three mandatory parameters. ## Example 2See one more example of offset() function without having height and width parameters. We will use the same data table for this example and only arguments are changed.
=OFFSET(A1,2,3)
See that the offset() function has been returned 3000 on executing the OFFSET() function. ## Using height and width parameterIn this example, we will use the height and width (optional) parameters in the offset() function. See how differently its outcome receives. The offset() function usually returns multiple values, which is hold by using another function. This means you have to pass the offset() inside another function to hold the values returned by it. ## Example 1In this example, we will use this OFFSET() function inside the SUM() function to get the sum of values (reference cell) return by the offset() function. Here, we will use it for cell selection from multiple rows by entering the height value to 2.
See the few simple steps for it below:
See that it has returned the sum of the cells (D6, D7) whose reference return by the offset(). The sum has returned, i.e., 8100. You have now got this how the offset function works with optional parameter. ## Example 2See one more example of the offset() function with the optional parameters (height and width). In this example, we will use the offset() function inside the AVERAGE() to get the average of values (reference cell) return by the offset() function. This time, we will use it for cell selection from multiple columns by entering the width value to 2.
See the few simple steps for it below:
By using the above offset() formula with SUM() function, calculated result 6100 has returned. ## Example 3: Sum all cells of a columnThere are 5 columns containing 5 weeks of data in this Excel table, where each column contains 6 days of data. Illustrate an example to sum all cells of a column using the offset() function.
It will go for the same row and the second column from the starting point, then calculate the sum of six cells of that column.
Here, the sum is calculated through the offset() function. The offset() function is probably the most confusing function in Excel. ## Example 3: Sum the cells of a rowIn this Excel table, you can see that there are 7 rows containing 6 different days data, where each column contains one week of data. With the help of a simple example, we will illustrate to sum the all cells (that contains some data) of a row using the offset() function.
It will go for the fifth row and the first column from the starting point, then calculate the sum of five cells with respect to the fifth row.
Here, the sum is calculated through the offset() function. Next TopicCompound interest formula in Excel |