Offset Function

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,603
Office Version
  1. 2021
Platform
  1. Windows
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:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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:
Upvote 0
Thanks for the explanation. It makes perfect sense and your explanation is simple to follow
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top