I am trying to learn variable ranges using =OFFSET but I cannot grasp the concept of why - apparently - the same formula produces two different results. It's obviously dependent on which cell is active but I cannot see where the formula references that. Any (simple) explanation welcomed!
Hello,
Here's my understanding.
In your formula, from Cell A2, you offset 0 rows and 0 columns and then extract 12 rows and 1 column.
Therefore the result is an ARRAY. To visualize it, go in your formula, press F2 then F9.
Now because an array is returned, and you are in a single cell, let's say E10, Excel is returning the 9th value of that array because it started in A2. In E9, it will be the 8th value and so on...
If you were to validate your formula as an Array Formula with CTRL+SHIFT+ENTER, you would get "January" every time (when selecting only one cell) because this is the first value of the array.
If you select E10:E22, type your formula and validate with CTRL+SHIFT+ENTER, you'll get ALL the results.
Not sure if my explanation is clear or not...
GC Excel
If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.
=OFFSET(A$2,0,0,COUNTA(A:A)-1,1)
It's because the height argument, COUNTA(A:A)-1, returns a number >1, in this case 12, and the formula is entered in a single cell so it returns the value that is within the implicit intersection of where the formula is entered.
If you enter the formula in E15 you'll get an error because the formula is no longer within an implicit intersection of the data.
What are you trying to do with that formula?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Got it! Finally. Thanks for taking the time, it's appreciated.
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks