## Last column value

727 Views 23 Replies Latest reply: Jan 5, 2013 10:39 AM by Barry
Previous Next
Calculating status...
Currently Being Moderated
Jan 1, 2013 3:44 PM

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.

• Level 7 (28,185 points)
Currently Being Moderated
Jan 1, 2013 4:57 PM (in response to trexo)

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 (12,630 points)
Currently Being Moderated
Jan 1, 2013 5:00 PM (in response to trexo)

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 6 (12,630 points)
Currently Being Moderated
Jan 1, 2013 5:15 PM (in response to trexo)

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 6 (12,630 points)
Currently Being Moderated
Jan 1, 2013 5:43 PM (in response to trexo)

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 (28,185 points)
Currently Being Moderated
Jan 1, 2013 5:59 PM (in response to trexo)

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 (12,630 points)
Currently Being Moderated
Jan 1, 2013 6:15 PM (in response to trexo)

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 (29,095 points)
Currently Being Moderated
Jan 2, 2013 12:33 AM (in response to trexo)

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 6 (12,630 points)
Currently Being Moderated
Jan 2, 2013 7:36 AM (in response to trexo)

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

• Level 7 (28,185 points)
Currently Being Moderated
Jan 2, 2013 11:00 AM (in response to trexo)

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 Next

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.