Choose the last number in a column of numbers

[RKW]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a spreadsheet with a set of data on electric car usage. Each row represents data collected on a different day with the bottom row in the spreadsheet being the latest data. One of the columns in the spreadsheet is Cumulative Electric Consumption per Mile. I want the last entry in that row to be automatically displayed in another cell on the spreadsheet that is always visible since the last row and/or Cum column are frequently not visible on the computer screen. I thought there would be a function to find or select the last number in a column of numbers, but I can't find any such function. Any ideas?

 

[CyberTaz]

Easy way: Apply the List Manager to your data range (Insert> List...),
including the Totals Row feature. Create a formula in the desired cell which
simply refers to the Total cell (such as =D99). The formula cell will
continue to update as the List increases in length.

Additional features you may want to familiarize yourself with:

Window> Split
Window> Freeze Panes

I'm not sure if you meant this or not, but in Mac Excel there is no way
[AFAIK] to have any single cell/formula remain visible regardless of where
you navigate to elsewhere in the sheet.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[RKW]

Bob, I'm not familiar with List Manager. I tried to use it but the column of interest does not have a sum at the bottom of the column as many of my other columns do so it did not work well the way I tried to implement it. I am currently using the VLOOKUP function to identify the row of interest by finding the latest date entered in Column A. However, this doesn't work for the column that looks at Individual Trip Electric Consumption per Mile because there is not an entry in every row for this column.

BTW, I am using Freeze Panes based on Cell C4, so Cells B1 and B2 are always visible on the computer screen.
Rod

 

[CyberTaz]

First, don't use your own calculations in the last row of a list. The
calculation row is interpreted as a record in the data if you do.

Second, with List Manager you don't need your own calculations anyway...
When you click in the range of data & select Insert> List use the checkbox
for "Total Row" on the 3rd step of the List Manager. If the List is already
imposed click the Total Row button on the List Toolbar. With that turned on
you can select any cell in the Total Row to select the summary operation of
your choice for any & all columns, so....

Next, click the Total Row cell for the date column, click the button that
appears on the right edge of the cell to open the function list & select the
MAX function. Then enter an expression in a cell at the top of your sheet
that simply says =whatever the cell reference is for the cell containing the
MAX function. The result will automatically update as the data in the list
is added/changed.

I'm not sure this is the ultimate solution to what you want but it may be
helpful in getting there.

HTH |:>)
Bob Jones
[MVP] Office:Mac