Can a formula use the last number in a column?

Question

DougWW Author

Level 1

25 points

Can a formula use the last number in a column?

Is it possible to have a formula work with the last number in a column or row automatically?

For example:

Col A Col B

Jan 20

Feb 21

Mar 18

Apr 16

May

Jun

Jul

I would like to be able to have a formula use the last number in column B. I will be adding numbers to the list each month. And sorting the numbers is not realistic because of the month-to-month aspect.

I know I can edit the formula each month, but automatic is less error prone. There doesn't seem to be a "last" function. I'm working in the latest Numbers version, and would prefer not to move to Excel.

Any suggestions? Thanks.

..Doug

Posted on Feb 12, 2016 10:13 AM

Reply

Answer 1

Best reply

SGIII

Level 8

35,264 points

Feb 12, 2016 10:43 AM in response to DougWW

If you don't have blank cells in the column, you can do something like this:

=INDEX(Table 1::B,COUNTA(Table 1::B)+1)

The +1 is needed if there is one Header Row.

SG

Reply

Answer 2

DougWW Author

Level 1

25 points

Feb 12, 2016 11:47 AM in response to SGIII

This is really useful, Thanks.

I guess I'm still in Excel's mindset. I hadn't thought of using more than 1 table. I've learned something today.

I was working along using CountA to find the length of the number list, but hadn't figured out how to put a variable into a row designation in a formula. Your solution is much more intuitive.

Thanks again..

Reply

Answer 3

SGIII

Level 8

35,264 points

Feb 12, 2016 12:55 PM in response to DougWW

Glad it works for you. As you probably have discovered, tables are much more central to the Numbers mindset than they are to Excel (although they're often useful in Excel too). Footer Rows in Numbers are also a handy place to put formulas if it's more convenient to have them in the same table as the data they refer to. Thanks for the green tick!

SG

Reply

Answer 4

DougWW Author

Level 1

25 points

Feb 12, 2016 12:56 PM in response to SGIII

Okay, I've learned something else here too. I will experiment more with the footer rows.

Much appreciated. You earned the green tick twice over.

Reply