Fearless: Offset Function With Sum, Average, VLOOKUP

Offset Function With Sum, Average, VLOOKUP

OFFSET function in Excel:-

In this tutorial i am going to show one of the most powerful function of excel -The Offset Function.

What is offset in excel ? the OFFSET formula returns a reference to a range that is offset from a starting cell or a range of cells by a specified number of rows and columns.

The Excel OFFSET function may be a bit tricky to get, so let's go over a short technical explanation first (I'll do my best to keep it simple) and then we will cover a few of the most efficient ways to use OFFSET in Excel.

Excel OFFSET function - basic uses
Using OFFSET in Excel - formula examples
Excel OFFSET and SUM
OFFSET and AVERAGE / MAX / MIN
Excel OFFSET formula to create a dynamic range
Excel OFFSET and VLOOKUP
OFFSET function - limitations and alternatives

The syntax of offset function is as

=offset(reference, Row, clos, height, width)

The first three arguments are required and lat two are optional.All of the arguments can be references to other cells or results returned by other Excel formulas.

Required arguments:

Reference - a cell or a range of adjacent cells from which you base the offset. You can think of it as the starting point.

Rows - The number of rows to move from the starting point, up or down. If rows is a positive number, the formula moves below the starting reference, in case of a negative number it goes above the starting reference.

Cols - The number of columns you want the formula to move from the starting point. As well as rows, cols can be positive (to the right of the starting reference) or negative (to the left of the starting reference).

Optional arguments:

Height - the height, in number of rows, of the returned reference.
Width - the width, in number of columns, of the returned reference.

Both the height and width arguments must always be positive numbers. If either is omitted, the height or width of the starting reference is used.

A dynamic SUM / OFFSET formula:-

When working with continuously updated worksheets, you may want to have a SUM formula that automatically picks all newly added rows.

Suppose, you have the source data similar to what you see in the screenshot below. Every month a new row is added just above the SUM formula, and naturally, you want to have it included in the total. On the whole, there are two choices - either update the range in the SUM formula each time manually or have the Excel OFFSET formula do this for you.

Since the first cell of the range to sum will be specified directly in the SUM formula, you only have to decide on the parameters for the Excel OFFSET function, which will get that last cell of the range:

Reference - the cell containing the total, B9 in our case.
Rows - the cell right above the total, which requires the negative number -1.
Cols - it's 0 because you don't want to change the column.

Excel OFFSET formula to sum the last N rows:-

In the above example, suppose you want to know the amount of all items in table for the last N rows rather than grand total. You also want the formula to automatically include any new rows you add to the sheet.

For this we can use Excel OFFSET in combination with the SUM and COUNT / COUNTA functions:

=SUM(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

he following details can help you understand the formulas better:

Reference - the header of the column whose values you want to sum, cell B1 in this example.

Rows - to calculate the number of rows to offset, you use the COUNT function.

COUNT returns the number of cells in column B that contain numbers, from which you subtract the last N months (the number is cell E1), and add 1.

Excel OFFSET function with AVERAGE, MAX, MIN:-

In the same manner as we calculated the last N rows, you can get an average of the last N rows as well as find their maximum or minimum values. The only difference between the formulas is the first function's name:

=AVERAGE(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MAX(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MIN(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))