All replies

Jerrold Green1 Jan 1, 2013 4:57 PM
in response to trexo HelpfulTR,
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

Wayne Contello Jan 1, 2013 5:00 PM
in response to trexoyou 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

trexo Jan 1, 2013 5:07 PM
in response to Jerrold Green1Thank 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.

trexo Jan 1, 2013 5:08 PM
in response to Wayne ContelloThank you Wayne for the reply. Is there a simple formula I can put in one cell that will get the value?

Wayne Contello Jan 1, 2013 5:15 PM
in response to trexotrexo,
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

trexo Jan 1, 2013 5:27 PM
in response to Wayne ContelloTried 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.

trexo Jan 1, 2013 5:41 PM
in response to trexoI 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.

Wayne Contello Jan 1, 2013 5:43 PM
in response to trexotrexo,
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

Jerrold Green1 Jan 1, 2013 5:59 PM
in response to trexotrexo 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

Wayne Contello Jan 1, 2013 6:15 PM
in response to trexotrexo,
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

Barry Jan 2, 2013 12:33 AM
in response to trexoHi 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 rowoffset in the OFFSET function. Zero ( 0 ), the columnoffset value in the formula, means 'the same column as the base, B1'.
Your Excel formula:
=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

trexo Jan 2, 2013 6:18 AM
in response to trexoDoes 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.
Thank you for your patience!

Wayne Contello Jan 2, 2013 7:36 AM
in response to trexoWhat is in the last cell of the column? If that cell is empty then the formula will return "0"

Jerrold Green1 Jan 2, 2013 11:00 AM
in response to trexoTrexo,
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