MrExcel Publishing
Your One Stop for Excel Tips & Solutions

1st and 2nd non-zero values in a row


Posted by Michael on December 04, 2001 10:33 AM

I have a row of data approx 100 columns wide. The data is either a zero or text:
0 0 0 0 0 apple 0 0 0 0 0 orange 0 0 0 0 0

Is there a formula that can sit at the end of the row and give me the first non-zero value in the row and another formula that gives me the second non-zero value?



Posted by Mark W. on December 04, 2001 12:01 PM

1st non-zero value...

{=INDEX(A1:Q1,,SMALL(IF(ISTEXT(A1:Q1),ISTEXT(A1:Q1)*COLUMN(A:Q),""),1))}

2nd non-zero value...

{=INDEX(A1:Q1,,SMALL(IF(ISTEXT(A1:Q1),ISTEXT(A1:Q1)*COLUMN(A:Q),""),2))}

Note: These are array formulas which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.


Posted by Michael on December 04, 2001 1:09 PM

why does the first data have to be in column a?

I noticed that the formula doesn't work if you try to go b1:r1. However if I go a1:q1 it works. Any idea why?

Posted by Mark W. on December 04, 2001 1:20 PM

Re: why does the first data have to be in column a?

{=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1))}

...should work just fine! Don't forget to enter
this array formula using the Control+Shift+Enter
key combination.

Posted by Mark W. on December 04, 2001 1:36 PM

Oops! Now, I see your problem... Use this instead...

{=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R)-COLUMN(B:B)+1,""),1))}

Posted by Mark W. on December 04, 2001 1:39 PM

If you prefer... You can also use...

{=INDIRECT(ADDRESS(ROW(),SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1)))} {=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R)-COLUMN(B:B)+1,""),1))} : {=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1))} : ...should work just fine! Don't forget to enter