SUM(OFFSET to create list

Question

Hi,

Can someone explain what is actually happening, I have to similar formulas, (thanks Hans)

SUM(OFFSET(Table1[@],-1,0),1)

SUM(OFFSET(Table1[[#Headers],[Header]]:Table1[@],-1,0),1)

The first gives a sequential list 1,2,3 etc., but the second gives me a list that doubles at each row

1,2,4,8,,16 and so on. Can someone explain what's going on or direct to to some information.

Richard.

Answer 1

I assume that the second formula actually looks like this:

=SUM(OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0),1)

where ID is the name of the column.

Let's say that the header of the column is in A2. A1 should either contain text or be empty.

In A3, Table1[[#Headers],[ID]]:Table1[@ID] is equivalent to A2:A3.

OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0) is A1:A2.

SUM(OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0)) is SUM(A1:A2) = 0 because A1 and A2 don't contain numbers.

The entire formula returns 0 + 1 = 1.

In A4, OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0) i equivalent to A2:A4.

OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0) is A1:A3.

SUM(OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0)) is SUM(A1:A3) = 1 because A1 and A2 don't contain numbers and A3 = 1.

The entire formula returns 1 + 1 = 2.

In A5, OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0) i equivalent to A2:A5.

OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0) is A1:A4.

SUM(OFFSET(Table1[[#Headers],[ID]]:Table1[@ID],-1,0)) is SUM(A1:A4) = 3 because A1 and A2 don't contain numbers, A3 = 1 and A4 = 2.

The entire formula returns 3 + 1 = 4.

Etc.

Answer 2

Well I did have to ask,

thanks Hans, I'll go through it , just not this minute.

RD

Answer 3

No, I don't understand it either, but thought you might, it doubles the value each step.

RD

Answer 4

I assume that the first one looks like this:

=SUM(OFFSET([@ID],-1,0),1)

where ID is the name of a column.

[@ID] is the cell in the ID column in the same row as the cell with the formula.

OFFSET([@ID],-1,0) is the cell one row up from that (-1 row down = 1 row up, 0 columns to the right = same column).

SUM(OFFSET([@ID],-1,0),1) adds 1 to that.

So in each row, the formula adds 1 to the value in the previous row.

I don't understand the second formula, I suspect it should be different.