Ask Your Question
0

How do I find the bottom value in a column? [closed]

asked 2012-07-08 06:43:04 +0200

Asday gravatar image

updated 2014-03-19 22:24:30 +0200

manj_k gravatar image

Simply using a spreadsheet to manage my finances, and I'd like to be able to use my latest balance in formulae. It's always going to be the lowest (position within the sheet) populated cell, in a certain column.

So far, I've got as far as

=CELL("SHEET", CELL("ADDRESS", CONCATENATE("E", 3+MATCH(".+",SheetName.E3:E99999, -1))))

With "SheetName" being whatever the sheet is called, and "E" being the column. It simply returns "#REF!".

The 3 being added to the match is to account for the headers (column names, etc.)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 00:51:11.446924

2 Answers

Sort by » oldest newest most voted
1

answered 2012-07-09 08:52:39 +0200

JohnSUN gravatar image

Another variant of the same formula (without "array"):

=SUMPRODUCT(MAX(ROW(E1:E99999)*(E1:E99999<>"")))

(The function SUMPRODUCT() takes an array as a parameter and returns a single value)

edit flag offensive delete link more

Comments

Confirmed. This function will return the row number of last row in column 'E' that contains data.

qubit gravatar imagequbit ( 2013-03-13 10:09:15 +0200 )edit

I don't have any karma to upvote any of this, but thanks for asking this question, thanks for answering it, and thanks for adding the above comment which allowed me to find this.

subone gravatar imagesubone ( 2015-10-24 07:10:26 +0200 )edit
0

answered 2012-07-08 12:54:04 +0200

m.a.riosv gravatar image

An array formula, like next, find the last no blank row, in the column A:

{=MAX(IF(ISBLANK(A2:A10);0;ROW(A2:A10)))}

Ctrl+Shift+Enter to enter the formula, not only Enter.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2012-07-08 06:43:04 +0200

Seen: 6,696 times

Last updated: Jul 09 '12