Finding the last two non-zero value in a row

[ryan0521]

Good day,

Please help to have a formula to find the last two non-zero value in a row,


This is the result I want, please see below.

					last non zero	2nd non zero
3	2	1	3	0	3	1
0	0	2	1	0	1	2
1	0	1	0	0	1	1
2	2	0	1	0	1	2

<tbody>
</tbody>

 

[Aladin Akyurek]

In F2 just enter and copy dow:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:E2),A2:E2)

In G2 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:INDEX(A2:E2,MATCH(9.99999999999999E+307,1/ISNUMBER(1/A2:E2))-1)),A2:E2)

If you define BigNum in Name Manager as referring to:

=9.99999999999999E+307

we can have:

=LOOKUP(BigNum,1/ISNUMBER(1/A2:E2),A2:E2)

=LOOKUP(BigNum,1/ISNUMBER(1/A2:INDEX(A2:E2,MATCH(BigNum,1/ISNUMBER(1/A2:E2))-1)),A2:E2)

If so desired, wrap the formulas into an IFERROR call in order to avoid #N/A's when no non-zero values are available.

 

[ryan0521]

Thank you so much aladin,

Can you please explain it to me why this formula works, so that I can explain it thoroughly to others

 

[Aladin Akyurek]

Thank you so much aladin,

Can you please explain it to me why this formula works, so that I can explain it thoroughly to others

A. LOOKUP and kindred functions ignore error values in the references they are fed with if they possibly can. (A reference consisting solely of error values will cause such a function to return an error.)

B. LOOKUP and kindred functions with match-type set to 1 recruit most likelily some form of binary search (BS) algorithm, which is very fast.

C. When given a look up value, which is

MAX(reference)+Delta

where Delta is a very small number, in order to locate that value in the reference, LOOKUP hits the last numeric value in that reference, as a side of BS. Thus:

LOOKUP(MAX(A2:A4)+Delta,A2:A4)

will deliver

4

when A2:A4 houses say 6, 0.2, 4.

D. Since MAX is slow and returns error whenever an error value occurs in the reference it looks at, we choose a value improble to occur in the references of interest to us:

9.99999999999999E+307

So we get a construct like this:

LOOKUP(9.99999999999999E+307,Reference)

This big number is a constant of Excel itself, therefore a reliable, recognizable standard. See for more: http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

One of the formulas we are settled for is:

=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:E2),A2:E2)

>> (re-written)

=LOOKUP(9.99999999999999E+307,X,Y)

correlates the last numeric value of X with the value of Y that is at the same position.

The last numeric value of X must be a non-zero value (the requirement).

ISNUMBER(1/A2:E2) yields TRUE for values different from 0, otherwise FALSE.

1/ISNUMBER(1/A2:E2) yields a number for TRUE's and a #DIV/0! for FALSE's. LOOKUP correlates the position of the last number from X with a number at the same position in Y, which is A2:E2.

For more, see also:
http://www.mrexcel.com/forum/hall-f...-construct-return-last-match.html#post3304545
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998

Hope this helps.

 

[ryan0521]

Thank you so much.

 

[Aladin Akyurek]

Thank you so much.

You are welcome.