Offset Function

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,568
I would like to know how the 4th and 5th function in Offset works i.e height and width


eg
Code:
=SUM(OFFSET(J14,0,0,1,-5))


Your assistance is most appreciated
 
Last edited:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
It's pretty simple once you understand how the function fits together. The function selects a range of cells that has a marker cell at the top left. The position of that top/left cell is defined by the first three arguments.

The first gives the start point, the 2nd and 3rd arguments (rows and columns) set how far to move before you set the top left corner of the selected range.

The height and width arguments set the size of the selected range.

So J14, is offset by zero rows and zero columns, in your example. Which is to say, the selected cell starts at J14, moves zero rows down and zero columns across, and so stays at J14.

However, the function also selects a range of height 1. Fine, that's easy enough. A single cell is a range of height 1.

But it also has a width of -5. At this point you need to think like a computer. -5 columns to the right of J14 is actually 5 columns to the left.

It selects the range F14:J14, because that range is 5 columns wide.

The height and width will default to the height and width of the original selected reference.

A more intuitive example might be:

=OFFSET(A1,2,3,4,5)

The function starts at cell A1. Then it moves 2 cells down (ROWS) and three cells to the right (COLUMNS). The top left cell selected is D3. Then it selects a range that is 4 cells high (HEIGHT) and 5 cells wide (WIDTH) from cell D3. That is to say, D3:H6.
 
Last edited:

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,568
Thanks for the explanation. It makes perfect sense and your explanation is simple to follow
 

Watch MrExcel Video

Forum statistics

Threads
1,102,776
Messages
5,488,778
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top