30 Excel functions in 30 days: OFFSET

Yesterday in the marathon 30 Excel functions in 30 days we replaced text strings with the function REPLACE (REPLACE) and found out that she also knows how to insert characters.

On the 26th day of the marathon, we will study the function OFFSET (OFFSET). It returns a link of the specified size, spaced from the start link by the specified number of rows and columns.

So, let’s study the information and examples of using the function OFFSET (OFFSET), and if you have additional information and your own examples, please share them in the comments.

Function 26: OFFSET

Function OFFSET (OFFSET) returns a reference offset from the given reference by a specified number of rows and columns.

How can the OFFSET function be used?

Function OFFSET (OFFSET) can return a reference to a range and also work in conjunction with other functions. Use it to:

  • Find sales amounts for the selected month.
  • Summarize sales data for selected months.
  • Create dynamic ranges based on count.
  • Sum up sales for the last n months.

Syntax OFFSET

Function OFFSET (OFFSET) has the following syntax:

OFFSET(reference,rows,cols,[height],[width])

СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

  • reference (link) – a cell or range of adjacent cells.
  • rows (line_offset) can be positive (down from the start reference) or negative (up from the start reference).
  • cols (column_offset) – can be positive (to the right of the start link) or negative (to the left of the start link).
  • height (height) – number of rows in the returned link, must be positive.
  • width (width) – number of columns in the returned reference, must be positive.
  • If the arguments height (height) or width (width) are not specified, the dimensions of the original link are used.

Traps OFFSET

Function OFFSET (OFFSET) is recalculated each time any value changes in the Excel worksheet. Using this function in many formulas can greatly slow down the work. Instead of OFFSET (OFFSET) You can use the function INDEX (INDEX) to return a reference. INDEX (INDEX) recalculates the result only when its arguments change.

Example 1: Find the amount of sales for the selected month

Using functions OFFSET (OFFSET) You can return a range reference based on the start reference. In this example, we want to get the sum of the sales in cell G2.

  • The start link is cell C1.
  • The number of rows to shift is entered in cell F2.
  • The sales amounts are in column C, so the column shift is zero.
  • Argument height (height) is 1 line.
  • Argument width (width) is 1 column.

=OFFSET(C1,F2,0,1,1)

=СМЕЩ(C1;F2;0;1;1)

Cell H2 contains a formula very similar to the previous one that returns the name of the month. The only difference is the column shift: 1 instead of xnumx.

=OFFSET(C1,F2,1,1,1)

=СМЕЩ(C1;F2;1;1;1)

Note: In this example, the arguments height (height) and width (width) can be omitted, since we need a link of the same size as the start one.

30 Excel functions in 30 days: OFFSET

Example 2: Sum up sales for selected months

In this example, the function OFFSET (OFFSET) returns a reference to sales data for the selected month, and the function SUM (SUM) calculates the sum for this range. Cell B10 contains the number of the selected month 3, therefore, the result will be the amount of sales in March (Mar).

  • Initial Link A3: A6.
  • The row shift is equal to zero (if zero is not specified at all, the result will be the same).
  • The column shift corresponds to the value of cell B10.
  • The height and width are not specified because the resulting range must be the same size as the start range.

=SUM(OFFSET(A3:A6,0,B10))

=СУММ(СМЕЩ(A3:A6;0;B10))

30 Excel functions in 30 days: OFFSET

Example 3: Creating a Dynamic Range Based on a Count

You can use the function OFFSET (OFFSET) to create a dynamic range. In this example, we have created a named range MonthList with this formula:

=OFFSET('Ex03'!$C$1,0,0,COUNTA('Ex03'!$C:$C),1)

=СМЕЩ('Ex03'!$C$1;0;0;СЧЁТЗ('Ex03'!$C:$C);1)

30 Excel functions in 30 days: OFFSET

If another month is added to the list in column C, it will automatically appear in the drop-down list in cell F2, which uses the name MonthListas a data source.

30 Excel functions in 30 days: OFFSET

Example 4: Sum up sales for the last n months

In this final example OFFSET (OFFSET) works together with SUM (SUM) and COUNT (COUNT) to show the total for the last n months. As new values ​​are added, the formula result will automatically be adjusted to include the last month’s sales value. In cell E2, the number of months is 2, so the sums for August (Aug-10) and September (Sep-10) will add up.

  • The starting link is cell C2.
  • The number of rows to shift is calculated as follows:
    • count the number of numbers in column C,
    • subtract from the result the number of months specified in cell E3,
    • and add 1.
  • The sales are in column C, so the column shift is zero.
  • The height is entered in cell E3.
  • The width is 1 column.

=SUM(OFFSET(C2,COUNT(C:C)-E3+1,0,E3,1))

=СУММ(СМЕЩ(C2;СЧЁТ(C:C)-E3+1;0;E3;1))

30 Excel functions in 30 days: OFFSET

Leave a Reply