Structured reference and offset

[Jungan]

I am using offset and structured refs in a table to create dynamic arrays.

=OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1)

My intention with this formula is to create an array that grows in height for each row. But the return value is just a single value.

ROW([@number])-ROW(Table2[[#Headers];[number]])

this counter works fine on its own in a separate column in the table. It also retunrs the right value when nested which I verify with F9. But still the offset returns a single value array for all rows in the table?!

 

[Andrew Poulsom]

Welcome to MrExcel.

To return an array the formula must be confirmed with Ctrl+Shift+Enter. To sum the values for example:

=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))

 

[Jungan]

Thanks for your reply Andrew. I am sorry but I understand I didn't use the correct terminology. I am not looking for an array {} I merly want to create a vector(?) with offset. So no arrayformula is needed.
With offset I want to define a vector. I use the column header as reference, hard code the offset , and then I want to use the ROW([@number])-ROW(Table2[[#Headers],[number]]) to make the vector I define with offset one cell longer for each row. But it doesn't work. Offset just returns a single cell vector. Which is odd, because this expression does return a nice counter ROW([@number])-ROW(Table2[[#Headers],[number]]).
If I put this formula ROW([@number])-ROW(Table2[[#Headers],[number]]) in a separate column and refer to that in the offset formula, I do get a vector that grows for each row.

I want offset to return this: {10;20;30;40;50;60} but I only get a one value vector.
When I mark the counter formula in the offset it looks like it is counting up. But offset seams to ignore that input.

=OFFSET(Table2[[#Headers];[number]];1;0;{3};1) = 30
=OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1) = 30 on the third row

 

[Andrew Poulsom]

OFFSET returns a single value unless it is array-entered. What do you want to do with the result(s)?

 

[Jungan]

Lets say I want to sum a running total in a column I could use this =SUM($R$1:R1) and just drag the formula downwards. I want to achieve something similar with offset.
=SUM(OFFSET($R$1;0;0;ROWS($R$1:R1);1)) this formula would give the same result and the range defined by offset would grow in length for each row.
Now I want to get the same result by using structured refernces as I have my data in a table.
ROWS($R$1:R1)== ROW(R2)-ROW($R$1)== ROW([@number])-ROW(Table2[[#Headers];[number]]) This works fine, but when I put the structured reference in offset as a counter, offset cannot handle the result, why?
I am not really looking for a workaround, this is rather excel formula exploration

 

[Andrew Poulsom]

For summing this works for me:

=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))

as I said in Post #2.

 

[Jungan]

Ah, you are right, it does work when I use sum. What confused me was that if you mark the offset formula in this expression, regardless of the row and press F9 excel displays just a single value {3}. I was expecting something like this {1;2;3}.
=SUM(OFFSET(Table2[[#Headers],[number]],1,0,ROW([@number])-ROW(Table2[[#Headers],[number]]),1))

Ok so I had another goal with this but it doesn't work
=SUMPRODUCT(OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1);OFFSET(Table2[[#Headers];[mult]];1;0;ROW([@mult])-ROW(Table2[[#Headers];[mult]]);1))
when this does
=SUM(OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1);OFFSET(Table2[[#Headers];[mult]];1;0;ROW([@mult])-ROW(Table2[[#Headers];[mult]]);1))
and this
=SUMPRODUCT(OFFSET($F$1;1;0;ROWS($K$1:K1);1);OFFSET($F$1;1;0;ROWS($K$1:K1);1))

I shoul add that all these formulas are used inside table objects.

BR
Erik

 

[Andrew Poulsom]

Does this work for you?

=SUMPRODUCT(OFFSET(Table2[[#Headers],[number]],1,0,ROWS(Table2[[#Headers],[number]]:[@number])-1,1),OFFSET(Table2[[#Headers],[mult]],1,0,ROWS(Table2[[#Headers],[mult]]:[@mult])-1,1))

 

[Jungan]

It does I don't know why I overlooked the alternative of using ROWS instead. But there sure is something strange about the excel behaviour here.
In any case thanks alot for helping out Andrew!

 

[Andrew Poulsom]

Here's a discussion about the behaviour of ROW in SUMPRODUCT that you may find interesting:

excel - ROW() function behaves differently inside SUM() and SUMPRODUCT() - Stack Overflow