# Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

Posted by Brian Peterson on January 26, 2001 12:59 PM
Aladin, I just want to thank you for helping me and Jeff. We've been trying to figure this out for a while now. Here's what I've come up with using what you gave us:
{=INDEX([example.xls]Sheet1!\$A\$1:\$G\$1,MAX(IF(ISNA(MATCH(IF([example.xls]Sheet1!\$A\$1:\$G\$1<>0,[example.xls]Sheet1!\$A\$1:\$G\$1,""),[example.xls]Sheet1!\$A\$1:\$G\$1,0)),"",MATCH(IF([example.xls]Sheet1!\$A\$1:\$G\$1<>0,[example.xls]Sheet1!\$A\$1:\$G\$1,""),[example.xls]Sheet1!\$A\$1:\$G\$1,0))))}
What we're trying to do is have a formula that will pull from another sheet the last non-zero value. This formula works, with a small glitch that doesn't really hurt us too much since the schedule that we're pulling from doesn't put in the same number usually (instead it will just leave that cell blank). Now we're trying to make a function that will use this formula. We have someone here at work that is going to try and do it. He is extremely busy and I'm not sure we'll have it any time soon. I didn't know how hard or how long it would take, but could you maybe guide us how to write this function or maybe make one (something like LAST which acts similar to the MAX fucntion when it is ran). That is, if it isn't much work. If it is, don't worry about it. Jeff and I are part time process engineering assistants and are trying to get this done as soon as possible for our superiors. I really want to thank you for all your help. Thank you!

Posted by Aladin Akyurek on January 26, 2001 2:50 PM

Brian, Jeff & Denis (Is Denis a member of the gang?)

Here is the redesigned array-formula, supposed to eliminate the glitch that Jeff reported.

=IF(INDEX(A1:G1,COUNTA(A1:G1))>0,INDEX(A1:G1,COUNTA(A1:G1)),INDEX(A1:G1,MAX(IF(ISNA(MATCH(IF(A1:G1<>0,A1:G1,""),A1:G1,0)),"",MATCH(IF(A1:G1<>0,A1:G1,""),A1:G1,0)))))

Jeff: would you let me know if it really eliminates the glitch?

The modification above also applies to the second formula that I gave to Denis (the one that disregards the text values in de test range).

What task is LAST supposed to do? Judging by name only, it looks too much to the modified part above.

## the last non-zero value in a row: the final solution

Posted by Aladin Akyurek on January 27, 2001 5:09 AM
Array-enter