Formula help!

  • Thread starter Thread starter JohnQ
  • Start date Start date
J

JohnQ

hi all

can someone help me where i have a row of numbers that go
0, 0, 0, 0, 0, 5, 10, 5 etc... from cells D2:K2

i want a formula to put in A2 that returns the first non-
zero number, ie. it would return 5. it would also be
useful to have a formula that returns which column the
first non-zero number occurs in.

any ideas? thanks
JohnQ
 
one way:

First non-zero (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(rng,MATCH(TRUE,rng<>0,FALSE))

where rng is your range reference (e.g., D2:K2).

Column the first-non-zero occurs in (also array-entered):

=LEFT(ADDRESS(1,3+MATCH(TRUE,D2:K2<>0,FALSE),2,TRUE),1)

where 3 is the starting column number -1. If the rng extends past column
26:

=SUBSTITUTE(LEFT(ADDRESS(1, 3 + MATCH(TRUE,D2:AK2>0, FALSE), 2,
TRUE),2), "$", "")
 
Back
Top