Hi guys,

Need piece of advice.

I have to get the last number from the row that is more than 0. Sounds
a little complicated but actually it is for example:

CEll A1 - 10
CEll A2 - 0
CEll A3 - 15
CEll A5 - 0

In Cell A5 I should have 15.

Does somebody have any idea how to get it?

Thanks a lot in advance,

Irina.


--
Irina
------------------------------------------------------------------------
Irina's Profile: http://www.excelforum.com/member.php...fo&userid=8685
View this thread: http://www.excelforum.com/showthread...hreadid=528257

Try...

=LOOKUP(2,1/(A1:A40),A1:A4)

Hope this helps!

In article ,
Irina wrote:

Hi guys,

Need piece of advice.

I have to get the last number from the row that is more than 0. Sounds
a little complicated but actually it is for example:

CEll A1 - 10
CEll A2 - 0
CEll A3 - 15
CEll A5 - 0

In Cell A5 I should have 15.

Does somebody have any idea how to get it?

Thanks a lot in advance,

Irina.

Hi Irina,

Something like this ...

=LOOKUP(2,1/(A1:A50),ROW(A1:A5))

Change the range (A1:A5) to suit.

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Irina" wrote in
message ...

Hi guys,

Need piece of advice.

I have to get the last number from the row that is more than 0. Sounds
a little complicated but actually it is for example:

CEll A1 - 10
CEll A2 - 0
CEll A3 - 15
CEll A5 - 0

In Cell A5 I should have 15.

Does somebody have any idea how to get it?

Thanks a lot in advance,

Irina.


--
Irina
------------------------------------------------------------------------
Irina's Profile:
http://www.excelforum.com/member.php...fo&userid=8685
View this thread: http://www.excelforum.com/showthread...hreadid=528257

Oops! I gave you one to find the row number. Use this instead...

=LOOKUP(2,1/(A1:A50),A1:A5)

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Irina" wrote in
message ...

Hi guys,

Need piece of advice.

I have to get the last number from the row that is more than 0. Sounds
a little complicated but actually it is for example:

CEll A1 - 10
CEll A2 - 0
CEll A3 - 15
CEll A5 - 0

In Cell A5 I should have 15.

Does somebody have any idea how to get it?

Thanks a lot in advance,

Irina.


--
Irina
------------------------------------------------------------------------
Irina's Profile:
http://www.excelforum.com/member.php...fo&userid=8685
View this thread: http://www.excelforum.com/showthread...hreadid=528257

Irina,
There may be better ways but one would be to create an array formula
such as

{=INDEX(A1:D1,1,MAX(COLUMN(A1:D1)*(A1:D10)))}

Note: Do not enter the braces at the start and end but commit the
formula with Ctrl-Shift-Enter.

Here's how it works:
COLUMN(A1:D1) yields an array of column numbers {1,2,3,4}
(A1:D10) yields an array of TRUE values if greater than zero, else
FALSE {TRUE, FALSE, TRUE, FALSE}
Multiplying the two arrays yields a new array of column numbers and
zeros
{1,2,3,4} * {TRUE, FALSE, TRUE, FALSE} = 1*1, 2*0, 3*1, 4*0 or
{1,0,3,0}
MAX of this yields the column with the highest column number with
non-zero values
INDEX uses that value to pick the value in that column

Note: If your data does not start in column A, you'll need to adjust
the formula a bit since index expects the first value to be in position
1.

Hope that helps.

- John


MAX(COLUMN(A1:D1)*(A1:D10))

Clever. I don't understand why it works. Why look up "2" and why does
the array results from 1/(A1:A40) only yield two non-error numbers
even if the data are changed so there's only one zero?

- John

Have a look at Aladin's explanation in the following link...

http://www.mrexcel.com/board2/viewto...ys=0&postorder
=asc&start=0

Hope this helps!

In article .com,
"John Michl" wrote:

Clever. I don't understand why it works. Why look up "2" and why does
the array results from 1/(A1:A40) only yield two non-error numbers
even if the data are changed so there's only one zero?

- John