Formula to let me know if my sales drop off

[tonywatsonhelp]

Hi Everyone,
I thought this would be simple but it proving to be a challenge please help if you can.

I have a set of data that shows me how many items of a product I sell each month
What I want to identify if if a product was selling well, suddenly stoped selling then began selling again,
So let takr range "BE53:CO53" in in cell Cp35 id like a formula that tells me if sales drop off,
So it starts at BE53, If BE53 is a zero the it finds the first cell with a value greater than zero, If it doesn't find one then it returns "No Drop"
if it find a cell where the value is greater than zero, then it looks to see if any cells after that cell are zero, If it doesn't find one then it returns "No Drop"
If it find another zero then it looks to see if after that zero there is a cell with a value greater than zero, If it doesn't find one then it returns "No Drop"
if it find on it return "Contains Drop"

Please help if you can
Thanks
Tony

 

[DanteAmor]

Try the following UDF:

Function drop_off(rng As Range)
  Dim i As Long, j As Long, k As Long, n As Long, z As Long
  Dim cIni As Long, cFin As Long, nRow As Long
  Dim res As String
  
  nRow = rng.Cells(1).Row
  cIni = rng.Cells(1).Column
  cFin = rng.Columns.Count + cIni - 1
  For i = cIni To cFin
    If Cells(nRow, i) <> "" And Cells(nRow, i) = "0" Then
      If i < cFin Then z = i + 1 Else z = i
      n = WorksheetFunction.Count(Range(Cells(nRow, z), Cells(nRow, cFin)), ">0")
      If n = 0 Then
        'So it starts at BE53, If BE53 is a zero the it
        'finds the first cell with a value greater than zero,
        'If it doesn't find one then it returns "No Drop"
        res = "No Drop"
        Exit For
      Else
        For j = z To cFin
          If Cells(nRow, j) > 0 Then
            If j < cFin Then z = j + 1 Else z = j
            n = WorksheetFunction.Count(Range(Cells(nRow, z), Cells(nRow, cFin)), "=0")
            If n = 0 Then
              'if it find a cell where the value is greater than zero,
              'then it looks to see if any cells after that cell are zero,
              'If it doesn't find one then it returns "No Drop"
              res = "No Drop"
              Exit For
            Else
              If j < cFin Then z = j + 1 Else z = j
              For k = z To cFin
                If Cells(nRow, k) <> "" And Cells(nRow, k) = 0 Then
                  If k < cFin Then z = k + 1 Else z = k
                  n = WorksheetFunction.Count(Range(Cells(nRow, z), Cells(nRow, cFin)), ">0")
                  If n = 0 Then
                    'If it find another zero then it looks to see if after that zero
                    'there is a cell with a value greater than zero,
                    'If it doesn't find one then it returns "No Drop"
                    res = "No Drop"
                  Else
                    'if it find on it return "Contains Drop"
                    res = "Contains Drop"
                  End If
                  Exit For
                End If
              Next
              If res <> "" Then Exit For
            End If
          End If
        Next
        If res <> "" Then
          Exit For
        End If
      End If
    ElseIf Cells(nRow, i) > 0 Then
            If i < cFin Then z = i + 1 Else z = i
            n = WorksheetFunction.Count(Range(Cells(nRow, z), Cells(nRow, cFin)), "=0")
            If n = 0 Then
              res = "No Drop"
              Exit For
            Else
              For k = z To cFin
                If Cells(nRow, k) <> "" And Cells(nRow, k) = 0 Then
                  If k < cFin Then z = k + 1 Else z = k
                  n = WorksheetFunction.Count(Range(Cells(nRow, z), Cells(nRow, cFin)), ">0")
                  If n = 0 Then
                    res = "No Drop"
                  Else
                    res = "Contains Drop"
                  End If
                  Exit For
                End If
              Next
              If res <> "" Then Exit For
            End If
    End If
  Next
  drop_off = res
End Function

Review the following scenarios:

Dante Amor
	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN																											CO	CP	CQ
53		0																																						No Drop
54		0		2																																				No Drop
55		0		2		0																																		No Drop
56		0		2		0		3																																Contains Drop
57
58				0																																				No Drop
59				0		2																																		No Drop
60				0		2		0																																No Drop
61				0		2		0		3																														Contains Drop
62		2																																						No Drop
63		2		0																																				No Drop
64		2		0		3																																		Contains Drop
65				2																																				No Drop
66				2		0																																		No Drop
67				2		0		3																																Contains Drop
Hoja4

Cell Formulas
Range	Formula
CQ53:CQ67	CQ53	=drop_off(BE53:CO53)

 

[tonywatsonhelp]

wow Dante, this is excellent,
thank you very much,
Tony

 

[DanteAmor]

Im glad to help you, thanks for the feedback.