Dynamic Range Ignoring Blanks

[3G]

I did a little digging and found a formula to allow a dynamic range, ignoring blanks. I'm wondering if someone can explain the Offset formula:


=OFFSET($F$9,0,0,MATCH("*",F10:F16,-1),1)


I understand the match piece where the * is a wild card, and, the -1 is a close match...but Offset still throws me for a loop. Add in the complexity of this being supporting the dynamic range piece, and, my head hurts.

 

[Luke M]

Remember that the OFFSET function can take 5 arguements defined as:

=OFFSET(StartingPoint, RowOffset, ColumnOffset, Height, Width)


Looking at first 3 from your example, we can see that we are starting at F9, and we are not offsetting by any amount. Thus, we are definitely going to include F9. The next question is, how big is the range we want to return?


The 5th argument is a given 1, so we know the range the formula will return will be 1 column wide. The only variable left is knowing how many rows to return.


The MATCH function is looking for the last cell within F10:F16 that contains text (note that it won't find numbers/values). It then returns that cells position. So, assuming that last cell is F12, the MATCH function will return the number 3.


The 3 then tells the OFFSET function to include 3 rows within the returned range. Altogether then, OFFSET is told to start at F9, do no offsetting, and return 3 rows and 1 column. Thus, the range F9:11 would be returned in this example.

 

[a-free-man]

This offset tool was great and I hadn't known about it. With it I was able to run regression scenarios dynamically, by changing just the value of one cell. Thanks.