First non-zero in a range

[Gilbert De Ceulaer]

How can I find the first non-zero value in a range ?
E.g. range a1:a5, all are 0, excepted a3 and a5. How do I find the value in a3
Can somebody help ?
Thanks in advance.
Gilbert

 

[Ron Rosenfeld]

How can I find the first non-zero value in a range ?
E.g. range a1:a5, all are 0, excepted a3 and a5. How do I find the value in a3
Can somebody help ?
Thanks in advance.
Gilbert

The *array-entered* formula:

=INDEX(A1:A5MATCH(TRUE,A1:A5,0))

will do that.

To *array-enter* a formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.


--ron

 

[Hiroshi Takashima]

I do not know an Excel function that will do the job but
easiest way is to use some macro.
1. right click on the toolbar and select Control Tool Box
2. click on Command Button that looks like a rectangle in
the tool box then move to the worksheet and click anywhere
within the worksheet -- Command Button 1 will appear
3. double click on the Command Button--Project-VBA code
sheet will appear
4. between between Private Sub CommandButton1_Click() and
End Sub write the following code (if you want the result
in Cells(1,7) which is the same as Range("G1")):

Dim intRow As Integer
Dim intCol as Integer
intRow = 1
intCol=1 '1 is Column A, 5 is Column E, etc.
Do Until Cells(intRow, intCol) = ""
If Cells(intRow, intCol) > 0 Then
Cells(1, 7) = Cells(intRow, intCol)
Exit Sub
End If
intRow = intRow + 1
Loop

5. switch back to Excel and exit design mode by clicking
on a tool that looks like triangle and straight rulers and
a pencil
6. when you click on the command button, your result(the
first non-zero) will appear in G1 or cells(1,7)

 

[Peo Sjoblom]

-----Original Message-----


The *array-entered* formula:

=INDEX(A1:A5MATCH(TRUE,A1:A5,0))

will do that.

To *array-enter* a formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

Ron forgot a comma between A5 and match in the formula

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

Ron forgot a comma between A5 and match in the formula

Regards,

Peo Sjoblom

Thanks for pointing that out.

Should be:

=INDEX(A1:A5,MATCH(TRUE,A1:A5,0))


--ron

 

[Gilbert De Ceulaer]

Dear Ron,
This gives me an "#N/A". Why ?
Actually, when I enter it with the wizard, it shows that it is the
MATCH-part that gives this error.
Regards,
Gilbert

 

[Peo Sjoblom]

Try this adaption

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

enter with ctrl + shift & enter

 

[Ron Rosenfeld]

Dear Ron,
This gives me an "#N/A". Why ?
Actually, when I enter it with the wizard, it shows that it is the
MATCH-part that gives this error.
Regards,
Gilbert

See my later posting -- I accidentally deleted a comma (pointed out by Peo).

Also, be sure you *array-enter* the formula:
Hold down <ctrl><shift> while hitting <enter> and see XL put braces {...}
around the formula.


--ron

 

[Gilbert De Ceulaer]

Dear Ron,
For other's people reference :
I used the correction from the beginning, but even then I got the
"#N/A"-answer.
The solution from Peo works.
Thank you both.
Gilbert