Previous 1 2 23 Replies Latest reply: Jan 5, 2013 10:39 AM by Barry
Level 1

Looking for simplest way to return last value in a column that is regularly added to.  No blanks between numbers in the column.  Would like to return the value to a given cell.  Can do this in excel but having difficulty in numbers.  Thanks.

Solved by Jerrold Green1 on Jan 4, 2013 7:20 PM Solved
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

#### All replies

• Level 7

TR,

Here's a simple implementation:

=OFFSET(B1, COUNT(B), 0) is the formula in the Footer Row cell displaying the last value.

Curious how you did it in Excel. Same method should work in Numbers, I would think.

Regards,

Jerry

• Level 6
iWork

you can determine how many rows there are in a column by using the function ROWS().  You can then use the offset() function to retrieve the value using the rows() function.

something like:

in the table on the left:

A1=OFFSET(Data Table :: \$A\$1, ROWS(Data Table :: A)-1, 0)

The table on the right contains the column you "regularly" update.

I hope this helps

• Level 1

Thank you Jerrold for your reply.  I have seen your similar posts and have tried the offset command.  Does the formula have to be in the footer?  I am not familiar with putting formulas in footers.  All my data is in column d.  I would like the value to appear in cell B4.  In converting my spreadsheets from excel to numbers this formula did not translate well.  In excel I used the following:

=index(D:D, Match(9.9999999999999E+307,D:D))

That formula put the last value in column D into whatever cell I put the formula.

• Level 1

Thank you Wayne for the reply.  Is there a simple formula I can put in one cell that will get the value?

• Level 6
iWork

trexo,

I provided a formula.  Can you provide any context as to specifically what you are trying to do OR the Excel formula you are using?

Wayne

• Level 1

Tried the formula you suggested and it returned "This formula contains invalid reference."  I included my excel formula in an earlier response.  I have a column of numbers that gets added to regularly.  The content starts at D7 and goes down.  I am trying to create a formula in B4 that will always contain the last number in column D.

Forgive my difficulty in making this conversion...

Tried the numbers manual but it was no help.

• Level 1

I am using the offset formula but it is returning the cell value 6 up from the bottom.  I have tried the formula on two different pages of data with the same result.

• Level 6
iWork

trexo,

you have to name the tables the same as I did... the right table was titled "Data Table".  If your table is not named that AND you copied the formula I posted you will get the error your reported because the table you are attempting reference, in fact, does not exist so the reference is invalid.

This will work better for you-- I hope

B4=OFFSET(\$D\$7, ROWS(D)-7, 0)

Wayne

• Level 7

trexo wrote:

Thank you Jerrold for your reply.  I have seen your similar posts and have tried the offset command.  Does the formula have to be in the footer?  I am not familiar with putting formulas in footers.  All my data is in column d.  I would like the value to appear in cell B4.  In converting my spreadsheets from excel to numbers this formula did not translate well.  In excel I used the following:

=index(D:D, Match(9.9999999999999E+307,D:D))

That formula put the last value in column D into whatever cell I put the formula.

Yikes, I can see why you weren't excited about using that formula. You can put that formula of mine anywhere you wish. I'm making the assumption that you would be capable of adjusting the formula for the actual location of your data. Header and Footer rows are handy tools for isolating the data from the labels and summaries. Crack open the Numbers User Guide and become familiar with some of the interesting features in Numbers that make life easier than with Excel. Headers and Footers are quite useful.

Jerry

• Level 6
iWork

trexo,

Jerry and I both presented solutions but now, after looking at Jerry's solution, I am not sure which one is the one you are looking for... the distinction is that Jerry's will return the last value from a column even if it is not in the last cell of the column.  Mine will only return the value in the last actual cell of the column.  My guess is that Jerry's solution is the one you want since it is more general-- that is if the last cell in the column is filled our formulas will both work.

Wayne

• Level 7

Hi trexo,

Jerry's original solution, reproduced here, may be placed in any cell on the same table as the list of values in column B.

=OFFSET(B1, COUNT(B), 0)

It assumes that the first value in the list is in cell B2 (one row below the label in Row 1), and that (as you specified) there are no gaps in the data from that cell to the last one containing a value.

COUNT(B) counts the numerical values entered in column B; the result is used as the row-offset in the OFFSET function. Zero ( 0 ), the column-offset value in the formula, means 'the same column as the base, B1'.

=index(D:D, Match(9.9999999999999E+307,D:D))

MATCH returns the position of the specified value in a list of values in a range.

The range here is column D, and the value to match (expressed as a base 10 number) is

99999999999999 followed by 294 zeroes, a rather large number, likely to be far greater than the largest number in column D.

MATCH in numbers can be set to find the largest close match, the smallest close match, or an exact match, with 'largest close match' the default choice. My assumption is that this is also true in Excel. If so, the expression in your Excel formula is looking for the "largest number in column D that is smaller than or equal to the search value."

IF the last number in the column is also the largest number in the column (and that number is smaller than 9.9999999999999E+307), then the formula will return the correct value. If the last value in column D is NOT also the largest value in column D, OR is larger than the search value, then the formula will not return the 'last value' in column D.

A Numbers similar to this would be:

=OFFSET(D1,MATCH(9.9999999999999E+307,\$D)-1,0)

Here's an example, showing the results. The formula above is in B2; the same formula with the cell references changed to E1 and \$E is in B3:

Note that both return the largest value in their column, not necessarily the 'last' value in that column.

Regards,

Barry

• Level 1

Does it matter that I am doing this on numbers on my ipad using icloud?  Wayne...I created a new spread sheet and reproduced your exact scenario and it returns a value of 0.  As I create the formula numbers is supplying the steps.  I am trying to turn that feature off so I can have complete control but cannot find that functionality.

• Level 6
iWork

What is in the last cell of the column?  If that cell is empty then the formula will return "0"

• Level 7

Trexo,

If my solution doesn't work for you, I'm curious why that is the case. Could you please describe what you expect, and what happened when you tried my solution.

Kind regards

Jerry

Previous 1 2