Offset Function

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,398
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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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,398
Thanks for the explanation. It makes perfect sense and your explanation is simple to follow
 

Forum statistics

Threads
1,089,202
Messages
5,406,813
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top