What does it do?

It returns a reference to a range, from a starting point to a specified number of rows, columns, height and width of cells

Formula breakdown:

=OFFSET(reference, rows, columns, [height], [width])

What it means:

=OFFSET(start in this cell, go up/down a number of rows, go left/right a number of columns, height of range, width of range)


The OFFSET function in Excel is one of the Lookup functions and is great if you want to reference a range of cells and use that reference to do a calculation.

Click here to see how the formula works visually & interactively 

It is often used when you need to reference a range that is moving or resizing.  For example, if we have daily sales going down one row per day, then we can Sum the last X transactions.

This is what I am going to show you in this tutorial below:

DOWNLOAD EXCEL WORKBOOK

STEP 1: We need to enter the Sum function as we are going to Sum the last 7 transactions

(We can also add the Average function if we want to show the Average of the last 7 transactions):

=SUM

 

STEP 2: We need to enter the Offset function so we can reference the range that we want to Sum:

=SUM(OFFSET

 

STEP 3: 1st Offset argument – Where do we want to to start our reference?

This is at the start of the Daily Sales list:

=SUM(OFFSET(D11

offset 1st argument

 

STEP 4: 2nd Offset argument – How many Rows down do we want to go?

This is where the COUNTA function is used to count how many transactions we have in total and go down to the last cell in our list.

So as we are starting at cell D11, we are going to go down 11 cells ( COUNTA(D11:D100) ) and end up after the last cell with a value.

NB: It is always a good idea to enter an ending range that is more than your last data cell.  That way as your new data get entered, we will be sure to capture it!

=SUM(OFFSET(D11, COUNTA(D11:D100)

offset 2nd argumentV2

 

STEP 5: How many of the last sales transactions do we want to Sum?  7

So we need to take away 7 from the COUNTA formula which means that our Offset function will go up by 7 cells:

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,

offset 2nd argument minus 7_v2

 

STEP 6: 3rd Offset argument – How many Columns to the right/left do we want to move?

We do not want to move to any Columns, so we simply enter 0

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,

 

STEP 7: 4th Offset argument – How High do we want our referenced data to be?

Since we want to Sum 7 transactions, then we need the [height] to be 7 cells high:

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,

offset 3rd argument

STEP 8: 5th Offset argument – How Wide do we want our referenced data to be?

We want to reference 1 Column only, so we need to enter 1 for the [width]

=SUM(OFFSET(D11, COUNTA(D11:D100)-7,0,7,1))

offset 4th argument

As we can see the Sum of the last 7 transactions from our example is $28,472.

offset answer

If we add more transactions at the bottom of our Sales list then it it automatically return us the last 7 transactions, without the need to update the formula 🙂

offset answer2

Offset - Last 7 Transactions

HELPFUL RESOURCE:

728x90-2

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Excel Subtotal Function – Filtered or Visible Valu... What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
Calculate Elapsed Time in Excel When you have two points in time and you want to calculate the amount of time elapsed, then you will need to use Excel´s TEXT functionSometimes data gets dumped into Excel with the following date & time format:24/01/2015  19:48:00.Using the TEXT function and enter...
Sumproduct & Weighted Averages A quick way to calculate the weighted average of two lists of data is to use the Sumproduct formula.  A weighted average can be used to determine the average salary of employees, the average grade of an exam or the average selling price of a company´s stock list, as can been seen...
INDEX Function with Data Validation To make the INDEX function in Excel interactive we can add a Data Validation drop down list.By referencing the second argument in the INDEX function - the Row Number - to a data validation list, will allow a user to choose the nth position of a list and return their respectiv...