Find last cell in a range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreatsheet that has various ranges. I have a cell that uses uses the (LARGE) function to return what was the last number in the range. It all works fine untill there is a reduction in the value.at this point is does not works. I should add that the values within the range are created by links to other sheets and not entered directly

I realise that what I'm really looking for is a way of returning the last non zero value entered in the Range

I have tried to use the offset system but without sucess. Does anybody know what I might be doing wrong

The name of the range is NetValuatio

thanks
 
Last non-zero value in a range (1 col x n rows):

=INDEX(NetValuation,MAX(IF(NetValuation<>0,ROW
(NetValuation))))

Array-entered.

HTH
Jason
Atlanta, GA
-----Original Message-----
I have a spreatsheet that has various ranges. I have a
Click to expand...
cell that uses uses the (LARGE) function to return what
was the last number in the range. It all works fine untill
there is a reduction in the value.at this point is does
not works. I should add that the values within the range
are created by links to other sheets and not entered
directly.
I realise that what I'm really looking for is a way of
Click to expand...
returning the last non zero value entered in the Range.
I have tried to use the offset system but without sucess.
Click to expand...
Does anybody know what I might be doing wrong.
 
Morgan,

Array enter (using Ctrl-Shift-Enter)

=INDIRECT(ADDRESS(MAX(IF(NetValuation<>0,ROW(NetValuation))),COLUMN(NetValua
tion)))

HTH,
Bernie
MS Excel MVP

Morgan Wasem said:
I have a spreatsheet that has various ranges. I have a cell that uses uses
Click to expand...
the (LARGE) function to return what was the last number in the range. It all
works fine untill there is a reduction in the value.at this point is does
not works. I should add that the values within the range are created by
links to other sheets and not entered directly.
I realise that what I'm really looking for is a way of returning the last
Click to expand...
non zero value entered in the Range.
I have tried to use the offset system but without sucess. Does anybody
Click to expand...
know what I might be doing wrong.
 
Morgan,

Not knowing exactly what your data looks like, I don't
know if this is precisely what you are looking for.
However, here's one way to do it without using the LARGE
function:

=OFFSET($A$1,SUM(IF($A$1:$A$15=0,0,1))-1,0)

Where:

$A$1 = The first cell in your range
$A$1:$A$15 = The range you are looking for non-zero values

Note, that you have to hit CTRL+SHIFT+ENTER after you have
typed the formula into the cell. However, if you have
zero values interspersed in your data, this will not work.

Eric
 
Back
Top