get the last number from the row

I

Irina

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.
 
Z

Zack Barresse

Hi Irina,

Something like this ...

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

Change the range (A1:A5) to suit.

HTH
 
Z

Zack Barresse

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

=LOOKUP(2,1/(A1:A5>0),A1:A5)
 
J

John Michl

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:D1>0)))}

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:D1>0) 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:D1>0))
 
J

John Michl

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

- John
 
Top