How to get the last value on a column?

M

Macaco

Hello:

Could anybody please tell me how to get the last value that has bee
calculated on a column?

IE:
Say the column has 100 spaces but only 50 have been calculated so fa
(Starting from the top & going down), how can I get Excel t
write/choose the last calculated value, say on top of the column fo
information purposes.

Any help would be appreciated.

THANK
 
M

Macaco

I hope I explained my request.
But just to be sure, what I want is to be able to choose the las
calculated value on the column so that I can use it for othe
computation on the same sheet.

THANK
 
D

Dave Peterson

You can use an array formula like:

=INDEX(A1:A100,MAX(IF(A1:A100<>"",ROW(A1:A100))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
M

Macaco

Mr. Peterson:

Thanks for the reply.
I went ahead & tried what you said, substituting the A for the colum
that I need but I just can't get it to work.

Here is my situation:
The value that I want to get is from N3 to N39 & I want to write th
last calculated value on N1. N2 is the name of the column.


THANKS again
 
D

Dave Peterson

Since you're not starting in row 1, how about:

=INDEX(N3:N39,MAX(IF(N3:N39<>"",ROW(N3:N39)-ROW(N3)+1)))
(Still array entered)

I don't understand what's in N2.
 
D

David McRitchie

Try one of these

Numeric value
=LOOKUP(9.99999999999999E+307,A:A)

Value
=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>""))))

These will pick the last value in the column, not the last calculated, not the last entered.
If N2 is to contain something like A:A then perhaps substitue
LEFT(N2) and substitute INDIRECT(N2) in the above (untested)
 
M

Macaco

Mr. Peterson:

I tried what you suggested & it works, BUT ran into another proble
thou.

I have an "IF" formula on each cell (N column) where it calculates
value, assuming that another cell (M column) has a number. When tha
cell doesn't have a number, then the formula returns a 0 on all th
cells that don't have a value yet. Well, that makes your suggestio
return a 0 since the last calculated value is a 0. I removed th
formula from the cells that were 0 & your suggestion works AOK.

All I am trying to do here is basically (BEING LAZY) automate th
spreadsheet. Before I placed the call for HELP, what I was doing wa
using the "LARGE" function since the #'s were progressively highe
every month, BUT last month there was a decline & my setup would not d
what I wanted.

Anyway, I do appreciate your suggestions.

THANK
 
D

Domenic

Hi,

try,

=LOOKUP(9.99999999E+307,IF(A1:A100<>0,A1:A100))

entered using CTRL+SHIFT+ENTER.

Hope this helps!
 
M

Macaco

Mr. McRitchie:

THANKS for the reply.

I did what you suggested & can't get it to work. I am assuming tha
where the A is that I should put in an N, right?

Anyway, the N2 cell really doesn't matter, that's just kind of like
name (Heading) for the N column.

THANKS again
 
M

Macaco

Ok, again, THANKS for the help.

I do have a ?

Why do we have to write "9.99999999E+307"?
Correct me if I am wrong PLEASE, but I am trying to learn.

The IF says to check cells from A1 to A100 for less than or greate
than 0, right? I did changed the A to N & from N3 to N39

THANKS again
 
D

Domenic

Macaco said:
*Ok, again, THANKS for the help.

I do have a ?

Why do we have to write "9.99999999E+307"?
Correct me if I am wrong PLEASE, but I am trying to learn.

The IF says to check cells from A1 to A100 for less than or greate
than 0, right? I did changed the A to N & from N3 to N39

THANKS again. *
Click to expand...

I believe "9.999999999E+307" represents the highest or one of th
highest possible numbers.

The IF statement says that for cells in the range A1 to A100 that d
not equal zero, lookup the stated value. And since no such valu
exists, it returns the last non-zero value.

Hopefully someone else can give you a more definitive answer
 
Top