Help with OFFSET

Cliff,

The built-in function browser provides details regarding this, and other, functions.

row() is a function that returns the row the formula resides in... So if the formula is in row 7, then row() returns 7 and therefore 3-row() = -4... or row()-2 = 5

offset allows to you get a value or range of values relative to a fixed location (called the base). The relative position is defined by the offset from that base as a row and column offset. Finally you can define the size of the returned value as a single cell (1 row and 1 column) to a range.

your formula adds the value in an absolute cell location ($F$1) to the sum of a range defined by the function

$F$1 + sum(<range>)

<range> = OFFSET(F5,3-ROW(),-1,ROW()-2)

I hope this helps. If you provide more context it will be possible to provide a less general explaination.

Hi Cliff,

It would be easier for us to help you if you would give us the exact coordinates of the example you quoted so we can see it in context. The function ROW() returns the row number of the row where the expression is used. if you were to find 3-ROW(), that would equate to ROW() less than the number 3. For rows beyond the third row, that would point to a row in the offset function that is above the base address.

Jerry

The point of using this formula in the checking register is to make the table so it can be sorted without messing up the "balance" column. The formula takes the starting balance and adds to it the sum of the entries in the "Amount" column starting at the topmost row and stopping at the current row.

You preferences in Numbers is to "Use header cells as cell references". "Balance 101" = the cell that has "Balance" in the header row and "101" in the header column. In other words, it is cell F3.

OFFSET(base, row offset, column offset, rows, columns)

Offset can give you a single cell or a range of cells. In this case it is giving you a range of cells.

Base = cell F3, as described above. The cell with the formula in it

Row Offset = 3-ROW()

• For the first entry in cell F3, it is 3-3 = 0 = no offset = the current row = row 3.
• For the second entry, F4, it is 3-4 = -1 = one row up = row 3
• For the third entry, F5, it is 3-5 = -2 = two rows up = row 3
• See the pattern. Always row 3

Column Offset = -1 = one column to the left of the base = the "Amount" column

So, the offset is to the cell in the "Amount" column in row 3 = cell E3

Rows is an optional parameter if you want the result to be a range. It specifies how many rows to include in the range

• For the first entry in cell F3, ROW()-2 = 3-2 = 1 = include only one cell. The result is therefore cell E3.
• For the second entry, cell F4, ROW()-2 = 4-2 = 2 = include two cells. The resulting range is therefore E3:E4
• For the third entry, cell F5, ROW()-2 = 5-2 = 3 = include three cells. The range is therefore E3:E5.

Columns is optional. It is not used in this formula. It works like row offset.

Ciiff,

In short, the expression: =$F$1+SUM(OFFSET(Balance 101,3-ROW(),-1,ROW()-2)) says to start with the Beginning Balance and add to it the SUM all the figures in the column immediately to the left of column F from Row three to the current row.

Since the additions to the account log are positive and expenditures are negative, the answer will reflect the current balance down to that row.

Jerry