how can I find the first non-zero cell

R

rebecca

From left to right how can I get a formula that will find the answer to each
row as stated so the formula returns the first non-zero cell?

0 0 0 0 42.5 answer 42.5
0 0 0 4.74 0 answer 4.74
101.5 102 0 0 0 answer 101.5

99.975 101 0 0 85.188 answer 99.975
0 74.5 0 0 0 answer 74.5
 
M

Mike H

Rebecca,
Try this array formula

=OFFSET(A1,0,MIN(IF(1:1>0,COLUMN(1:1)))-1,1,)

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.


Mike
 
L

Luke M

Try this array* formula:

=INDEX(A2:C2,1,MATCH(TRUE,A2:C2>0,0))

*formula must be confirmed using Ctrl+Shift+Enter, not just Enter.
 
J

Jacob Skaria

Hi Rebecca

Try the below formula in F1 and copy down as required..

=INDEX(A1:E1,MATCH(TRUE,INDEX(A1:E1<>0,),))

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing page numbers?? 0
How to count the numbers in a Word document? 0
Use & with two formulas 2
Counting Zeros in a row until encounter first non-zero value 9
Drop down list control value in vba 2
count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
If function across sheets prints value of cell 4
Vlookup and return sheet name also 2

Top