Offset Clarification

joeino

New Member
Joined
Oct 28, 2020
Messages
21
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi,

I am still new with offset and I am not sure what I am missing.

Ok, my formula is =OFFSET(A1,0,0,4,1) which basically means from A1, 0 rows, 0 columns and the height is 4 (A1:A4) and 1 width. How come I am getting different result even if I am using the same formula on 4 different cells?
I was expecting a result of 1 (value in A1) for all cells but it is showing otherwise.

Manifest Report 02.18.xlsb
ABC
111=OFFSET(A1,0,0,4,1)
222=OFFSET(A1,0,0,4,1)
333=OFFSET(A1,0,0,4,1)
444=OFFSET(A1,0,0,4,1)
Sheet3
Cell Formulas
RangeFormula
B1B1=OFFSET(A1,0,0,4,1)
B2B2=OFFSET(A1,0,0,4,1)
B3B3=OFFSET(A1,0,0,4,1)
B4B4=OFFSET(A1,0,0,4,1)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
If you want only One cell, Why your height is 4 not 1.
Excel Formula:
=OFFSET(A1,0,0,1,1)
 
Upvote 0
Using Excel 365:

AB
11
222
333
444
5xx
6y
1-Source
Cell Formulas
RangeFormula
B2:B5B2=OFFSET(A2,0,0,4,1)
Dynamic array formulas.

In previous versions of Excel, the formula will return the same vector {2;3;4;"x"} but will display only the top left hand value, i.e. 2.

Why do you expect the result 1 in each cell?
 
Upvote 0
Using Excel 365:

AB
11
222
333
444
5xx
6y
1-Source
Cell Formulas
RangeFormula
B2:B5B2=OFFSET(A2,0,0,4,1)
Dynamic array formulas.

In previous versions of Excel, the formula will return the same vector {2;3;4;"x"} but will display only the top left hand value, i.e. 2.

Why do you expect the result 1 in each cell?
Thank you for the reply.

I was expecting the result 1 in each cell because I am using the same formula in every other cells and the way I understand offset. Now I am sure that I still completely do not understand offset lol.
 
Upvote 0
Sorry, I didn't properly read your first post.

You're using a feature of Excel called Implicit Intersection. The example below might help illustrate what's going on:

Cell Formulas
RangeFormula
F1:F3,D4:D7,B1:B6F1=$A$2:$A$5


(I'm actually using =@$A$2:$A$5 to make this work in Excel 365. For earlier versions, just use: =$A$2:$A$5)

In this case, Excel returns the value based on the position of the row of the formula cell relative to the column A2:A5. (You can construct similar examples for a column's relative references to a row).

That's what's happening with your Offset, which creates a 4 high column of results. Depending on the relative position of your formula to A1:A4, you'll return either the 1st, 2nd, 3rd or 4th value, or #VALUE if your formula cell is outside rows 1-4.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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