Finding the Last Value in a Range

M

MT DOJ Help Desk

Excel 2000

I have the following data:

Cell Value
A1 5055
A2 5047
A3 5044
A4
A5 5039
A6 5037
A7 5032
A8 5028
A9 5026
A10
A11 5019
A12 5014
A13 5017
A14 5021
A15
A16
A17
A18
A19
A20
A21 Formula

The column starts out empty and the values are entered one day at a time.
The formula at the bottom of the column needs to always return the value in
the last non-blank cell in the A1..A20 range. For example, after the first
day, when only cell A1 is filled in, the formula should return 5055. It
should return 5047 the second day, 5044 on the 3rd and 4th days, 5039 on the
fifth day, etc. In the above example, the formula should return 5021--the
value of the last non-blank cell in the A1..A20 range.

Is there a function that will do this? If not, is there some other way to
accomplish this goal?

--Tom
 
F

Frank Kabel

Hi Tom

some solutions depending on your data:
1. If you have only values without blank cells between them you can use
COUNTA:
=INDEX(A1:A100,COUNTA(A1:A100))

2. If you have blank cells between your values (as your example
suggests) and you're looking for the last non-blank entry use the
following array formula (to be entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A100,MAX(IF(A1:A100<>"",ROW(A1:A100),0)))

3. If you have blank cells and are looking for the last numeric entry
in your range (taht is text entries are not counted) use the following
array formula
=INDEX(A1:A100,MAX(IF(ISNUMBER(A1:A100),ROW(A1:A100),0)))

HTH
Frank
 
M

MT DOJ Help Desk

Frank,

Thanks for the help. Option 1 is out because the data can contain blank
cells between values. However, Options 2 and 3 would both work with my
data. I only care about picking up numeric values, and even though the data
will not contain any text values (so Option 2 would work), I decided to go
with Option 3 because that provides more flexibility--if I add text values
in the future, it will still pick up only the numeric values.

--Tom

Frank Kabel said:
Hi Tom

some solutions depending on your data:
1. If you have only values without blank cells between them you can use
COUNTA:
=INDEX(A1:A100,COUNTA(A1:A100))

2. If you have blank cells between your values (as your example
suggests) and you're looking for the last non-blank entry use the
following array formula (to be entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A100,MAX(IF(A1:A100<>"",ROW(A1:A100),0)))

3. If you have blank cells and are looking for the last numeric entry
in your range (taht is text entries are not counted) use the following
array formula
=INDEX(A1:A100,MAX(IF(ISNUMBER(A1:A100),ROW(A1:A100),0)))

HTH
Frank

Excel 2000

I have the following data: [snip]
The column starts out empty and the values are entered one day at a
time. The formula at the bottom of the column needs to always return
the value in the last non-blank cell in the A1..A20 range. For
example, after the first day, when only cell A1 is filled in, the
formula should return 5055. It should return 5047 the second day,
5044 on the 3rd and 4th days, 5039 on the fifth day, etc. In the
above example, the formula should return 5021--the value of the last
non-blank cell in the A1..A20 range.

Is there a function that will do this? If not, is there some other
way to accomplish this goal?

--Tom
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use the content of a cell in the sum (or other) functions 2
Pick last date in a column if more than two similar dates 2
HOW TO USE THE { } IN A FORMULA 3
return last value of a sheet 4
Sum of a range that varies 1
Q: check a range values 2
Conditional Format based on range 3
how can i find a value in a range? 1

Top