Comparing the same sequence of numbers different from 0

[Luthius]

I have in a row 14 numbers. 0-4.
I would like to indicate "xxx", in case of appear in this line 5 numbers in sequence that are <> of 0

Example:
0 0 0 0 1 1 3 1 1 0 0 0 0 0 "xxx"
0 0 1 0 0 1 2 3 4 0 1 1 0 0
0 1 1 2 1 1 0 0 0 0 1 1 0 0 "xxx"

Each number is on a cell and the result (xxx) in other.
A:N is my range

Luthius

 

[mikerickson]

How about =IF(COUNTIF(A1:N1,"<>0")=5, "xxx", "")

 

[Sektor]

mikerickson, your code will be incorrect here:
0 1 1 2 1 0 0 0 0 0 1 1 0 0

This is because non-zero numbers must be in sequence as Luthius asks.

 

[mikerickson]

In that case

=IF(0 < SUMPRODUCT(--(COUNTIF(OFFSET(A1:E1,0,COLUMN(A1:J1)-1),"<>0")=5)),"xxx","")

 

[Comfy]

Option Explicit
Function NumStr(ByVal nString As String)
Dim i As Long, j As Long
Dim mString As String

j = 0
    For i = 1 To Len(nString)
        If Mid(nString, i, 1) > 0 Then
            If j< 5 Then mString = mString + Mid(nString, i, 1): j = j + 1
        Else
            If j< 5 Then mString = "": j = 0
        End If
    Next
    If j = 5 Then
        NumStr = "xxx"
    Else
        NumStr = ""
    End If
End Function

Excel Workbook
	A	B
1	00001131100000	xxx
2	00100123401100
3	01121100001100	xxx
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=NumStr(A1)
B2	=NumStr(A2)
B3	=NumStr(A3)

Doh! Mis-read the question and the part about each number being in it's own Cell.

 

[Sektor]

Comfy,
Your code will be incorrect in case: 1111111111111111111 'cause in sequence there more than 5 non-zero digits (see post #3).

 

[Luthius]

mikerickson I'm afraid of you.
I'm trying understand your code but...
could you?

 

[Comfy]

Comfy,
Your code will be incorrect in case: 1111111111111111111 'cause in sequence there more than 5 non-zero digits (see post #3).

Lithius doesn't actually state what to do if there are 6 or more consecutive numbers. So 11111111111111 would match his initial criteria.
But my code is wrong in that it looks at a String, not separate columns.

 

[Sektor]

Comfy, cite it:

...in case of appear in this line 5 numbers in sequence that are <> of 0

 

[Comfy]

Comfy, cite it:

It's just a question of interpretation.

But still irrelevant as my code is not applicable to the problem.