Last column value

" When I insert the formula in the footer of row D, that is the row where my data is, I get an error which says: formula cannot reference its own cell or depend on another formula that references this cell."

I'm assuming you mean "the footer row of column D".

The error message means that the formula's range, D:D, includes the last (regular) cell in column D. That would indicat that the bottom row, where you have placed the formula, is not a Footer row.

"Footer row" has a specific meaning in Numbers. It does not mean " the bottom row," although if there is only one Footer row, that is where it will be located on the table.

Numbers tables may have one or more Footer rows, to a maximum of five. To add a Footer row to a table, go Table (menu) > Footer rows > 1.

After adding the Footer row, copy the formula from the cell containing it, then paste it into the Footer row cell in that column.

Regards,

Barry

"I have 10 separate sheets with all data starting in cell D7. I amended your formula to be =offset(D7,Count(D),0). When I placed your formula in B4 the error came back: This formula contains an invalid refernce.

"I have gone back and re-entered your formula in the footer of 4 of the sheets. In all 3 of them a 0 is returned. In one of them the value 10 up from the bottom of the column is returned."

COUNT(D) counts all of the numerical entries in regular cells of column D. "regular" cells means any cells in the column that are not in Header rows or Footer rows.

Test 1: Replace =offset(D7,Count(D),0) with =COUNT(D). Compare the result with the number of data entries in and below cell D7. The OFFSET formula will return the value in the cell that many rows below D7 (eg. if the count is 1, the OFFSET formula will return the value from cell D8, 1 row below D7). The error message you received suggests the offset is more rows below D7 than there are rows (below D7) in the table.

"In all 3 of them a 0 is returned."

"all" would be 4, not 3. See the note on COUNT above. The formula is likely returning a value from a cell below the last one containing data. Empty cells return a value of zero.

"10 up from the bottom"

OFFSET counts done from the base cell, not up from the bottom row. How does "10 up from the bottom" turn out when expressed as "x down from the base cell". The base cell in the formula as shown above is D7.

Regards,

Barry

Jim,

If I'd known that you wanted to apply my solution to an Excel Worksheet, I wouldn't have given you the deisgn that I did. I make a habit of writing answers that take advantage of the special features of Numbers, which quite often won't work in an Excel Worksheet. When an Excel document is imported to Numbers the tables will require Numbers-izing to work with my suggestion because, as Barry notes, it is important to be precise with the use of Header and Footer rows.

I'm glad that Barry was here to respond to your comments while I was away.

Jerry

Hi trexo,

You wrote:

"While my data started in D7, the word "Balance" is listed in D6. When I adjust the formula to Offset(D6), Count(D),0 it all works! "

"Did not consider that Numbers would not have differentiated between words and numbers. My excel formula did not require that."

The change you note above has nothing to do with Numbers differentiating (or not) between words and numbers (it does, or more specifically, the COUNT function does, as noted below). The change you not in the first paragraph is due to the way OFFSET works.

OFFSET does what the name implies: It returns the value in a cell whose position is offset a specified number of rows and a specified number of columns from a base cell.

The syntax is: OFFSET(base,row-offset,column-offset)

In your original formula, the base was cell D7, the row-offset is the COUNT of (numerical) data entries in column D, and the column offset is zero.

Suppose that only one data entry had been made (in cell D7). COUNT counts that and returns the result (1) to OFFSET. OFFSET now sees:

OFFSET(D7,1,0) and knows it is to return the value in the cell that is one row below and zero rows to the right of the base, D7. It correctly returns the value (zero) in the empty cell, D8.

When you change the formula to OFFSET(D6,COUNT(D),0) you've made a change to the base, not to the COUNT.

COUNT now counts the single numerical data entry in column D (the one in D7), and OFFSET knows it is to return the value from the cell that is one row below and zero rows to the right of the base, D6. It correctly returns the value in D7, one row below and zero columns to the right of the (new) base, D6.

MORE on COUNT and the 'Excel formula':

COUNT ignores text values and counts only numeric values (including Date and Time values, Durations and numbers formatted as currency, etc.)

You Excel formula was: =index(D:D, Match(9.9999999999999E+307,D:D))

This formula does not use COUNT.

It 'ignores' text values only to the extent that it assigns them a numeric value of zero.

MATCH, given no parameter to specify what type of match to attempt, returns the position in a list of the closest match it can find for the search value (9.9999999999999E+307). "Closest match" here means 'the largest value equal to or smaller than' the search value.

If the list of values in column D is ordered so that the last value is also the largest value (less than or equal to the search value), the formula will return the last value in the list. (see column D, below).

If the list is ordered in any manner that places the largest value (less than or equal to the search value), then the formula will NOT return the last value in the list. It will return the largest value (less than or equal to the search value). (see column C, below).

Since the search value in the formula is essentially 10^308 (in standard notation, 1 followed by 308 zeros), it's fairly safe to say the formula will return the largest value in the list.

Col D formula shown at top of image.

Col C formula is the same, with C substituted for D in both places.

Regards,

Barry