6 Replies Latest reply: Feb 11, 2013 10:43 AM by Jerrold Green1
Level 1 (100 points)

I've been trying to get my head around using OFFSET and think I am getting there but my difficuly comes when trying to understand the following example:-

=\$F\$1+SUM(OFFSET(F5,3-ROW(),-1,ROW()-2))

I think I get the first part of the formula but I just do not understand what the 'F5,3-ROW(),-1,ROW()-2)' part is doing.  I'd be grtaeful if someone could enlighten me as I really see the benefits of using this feature in my steadsheet.

Many thanks,

Cliff

Brighton, UK

• Level 6 (17,554 points)

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.

• Level 1 (100 points)

Wayne,

Thanks for the reply but I'm still struggling.  I came across this formula in the Checking Register template that is shipped with Numbers.

I want to use the formula in a similar spreadsheet but just do not understand the connection between the range of numbers and whatever the -row() piece does.  I think I am completely misunderstanding the context.  Am I reight in saying that there is a value in the cell that is three removed from F5?  If so, I do not understand what the calculation 3-row() or 2-row() has on the end result.

I have used the formula builder in Excel and the function browser in Numbers but just not making any progress.

Regards,

Cliff

• Level 7 (29,960 points)

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

• Level 1 (100 points)

Hi Jerry,

If you create a spreadsheet using the Checking Register template, then the formula is descibed in colum 'F' (i.e. the Balance column).  I have pasted the formula from a brand new spreadsheet (Cell f3) below:-

=\$F\$1+SUM(OFFSET(Balance 101,3-ROW(),-1,ROW()-2))

This same formula is repeated throughout the column.....

Regards,

Cliff

• Level 6 (11,615 points)

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.

• Level 7 (29,960 points)

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