First non-zero in a range

  • Thread starter Thread starter Gilbert De Ceulaer
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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)
 
-----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.
Click to expand...

Ron forgot a comma between A5 and match in the formula

Regards,

Peo Sjoblom
 
Ron forgot a comma between A5 and match in the formula

Regards,

Peo Sjoblom
Click to expand...

Thanks for pointing that out.

Should be:

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


--ron
 
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
 
Try this adaption

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

enter with ctrl + shift & enter
 
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
Click to expand...

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
 
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
 
Back
Top