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
