Identifying Zero-to-Non-Zero Value and vice versa

[nsturk725]

Hi Guys -

Can someone help me with the following

[sample data attached]

What I Need To Do:
Phase In

Where a cell is zero, check to see if the cell values up until "Nov" are not zeros. (DO NOT INCLUDE "TTL")
If there are no zero values in that range (to the right of the zero), return the "month" of the cell where it first started not to be zero (the cell to the right of the zero), to the respective "Month of Action" row-cell.

Example = the first data row "month of action" value would be "Aug"
= the last row "month of action" value would be "Nov"

[ I need to detect where a cell first turns from a zero to a positive number and remains a non-zero value through "Nov"]


Phase Out
(the reverse of above)

For each row, if there is a cell with a zero, check to see if the values through "Nov" are zeros as well.
If they are zeros, return the "month" of the cell where the first zero started.

Example = the second data row "month of action" value should be "Oct"

[I need to detect where a positive number turns to a zero and remains a zero through "Nov"]


Thank you very much for any help you guys can provide.

See sample attachment

[Excel Fox]

Can you reattach your workbook after highlighting which June you are referring to. And also, give your reason why the action month should be what you say it is.

[nsturk725]

Hi There -

Please see the new attachment for the correct June Month + explanations.

I basically trying to detect the month in which an item Phases In (starts being produced) and Phases Out (no longer is being produced).

Thank you very much for any help you can provide.

[Excel Fox]

Here's a VBA solution



Function fPhaseInOutMonth(rngSource As Range) As Long

Dim lngCol As Long: lngCol = rngSource.Columns.Count
If rngSource(1, lngCol).Value = 0 Then
For lngCol = lngCol - 1 To 1 Step -1
If rngSource(1, lngCol).Value <> 0 Then
fPhaseInOutMonth = lngCol + 1
Exit Function
End If
Next lngCol
Else
For lngCol = lngCol - 1 To 1 Step -1
If rngSource(1, lngCol).Value = 0 Then
fPhaseInOutMonth = lngCol + 1
Exit Function
End If
Next lngCol
End If
fPhaseInOutMonth = 1

End Function

[nsturk725]

Here's a VBA solution



Function fPhaseInOutMonth(rngSource As Range) As Long

Dim lngCol As Long: lngCol = rngSource.Columns.Count
If rngSource(1, lngCol).Value = 0 Then
For lngCol = lngCol - 1 To 1 Step -1
If rngSource(1, lngCol).Value <> 0 Then
fPhaseInOutMonth = lngCol + 1
Exit Function
End If
Next lngCol
Else
For lngCol = lngCol - 1 To 1 Step -1
If rngSource(1, lngCol).Value = 0 Then
fPhaseInOutMonth = lngCol + 1
Exit Function
End If
Next lngCol
End If
fPhaseInOutMonth = 1

End Function



Thank you so much. The code works wonderfully!

I appreciate it greatly! Sorry I did not get back to you sooner.