Hi all. I'm a relative newbie to using Excel and am stumped.

I'm trying to find the last (non-zero) value in a column. The cells
all have a function in them presently like =IF(C80,SUM(B8:C8)," ")
presently so that the cell truly is not empty. I've tried using
=LOOKUP(2,1/(D2:D3500),D2:D350) but it displays nothing since all the
cells have a value (the function above).

How do I find the last cell having a numeric value (which is what the
first function provides)? Any help is greatly appreciated.

It's because you don't use empty as opposed to space " " is a space and it
is better to use "" instead, change IF to

=IF(C80,SUM(B8:C8),"")


then change LOOKUP to

=LOOKUP(2,1/(D2:D35<""),D2:D35)

--

Regards,

Peo Sjoblom





wrote in message
oups.com...
Hi all. I'm a relative newbie to using Excel and am stumped.

I'm trying to find the last (non-zero) value in a column. The cells
all have a function in them presently like =IF(C80,SUM(B8:C8)," ")
presently so that the cell truly is not empty. I've tried using
=LOOKUP(2,1/(D2:D3500),D2:D350) but it displays nothing since all the
cells have a value (the function above).

How do I find the last cell having a numeric value (which is what the
first function provides)? Any help is greatly appreciated.

Peo,

That fixed it. Thanks for answering so quickly.

the lookup formula as configured is ingenious But i was trying to find the
last row in one column I used
=MATCH(LOOKUP(25,1/(A1:A20<""),A1:A20),A1:A20)
it gives the row number of the apparent last cell(mine is 9th row-ok)

But I would like to know
1. instead of 2 as lookupvalue I used some other number - is it ok
2. what is the explanation of the term 1/a1:a20<""
I understand a1:a20<"" means that any cell in A1:a20 is not equal to blank
but I did understand the nuance of 1/(partiuclarly that backward slash which
is normally division sign)
when I typed in any cell
=1/A1:A20<""
and hit control+shift+enter
it gave TRUE
and copy down . Then
upto "9" rows the results are TRUE and then #VALUE!
I get a vague idea but I would like to understand better.

apologise for bothering you and kind regards.

"Peo Sjoblom" wrote in message
...
It's because you don't use empty as opposed to space " " is a space and it
is better to use "" instead, change IF to

=IF(C80,SUM(B8:C8),"")


then change LOOKUP to

=LOOKUP(2,1/(D2:D35<""),D2:D35)

--

Regards,

Peo Sjoblom





wrote in message
oups.com...
Hi all. I'm a relative newbie to using Excel and am stumped.

I'm trying to find the last (non-zero) value in a column. The cells
all have a function in them presently like =IF(C80,SUM(B8:C8)," ")
presently so that the cell truly is not empty. I've tried using
=LOOKUP(2,1/(D2:D3500),D2:D350) but it displays nothing since all the
cells have a value (the function above).

How do I find the last cell having a numeric value (which is what the
first function provides)? Any help is greatly appreciated.