count cells since last non-zero value

[Guest]

I would like to know (count) the number of cells since the last non-zero value.

An example would be my data is in cells A1:A10 with A10 representing the
most recent value.
Data examople as follows for A1:A10 respectively;

2, 3, 2, 3, 0, 1, 4, 0, 0 ,0

In this example A7 has the most recent non-zero value.
In A11 I want to have a formula that tells me that its been 3 zero data
points since the last value (which is 4).

Rgds,

bruce

 

[bplumhoff]

Hi Bruce,

Enter into A11:
=ending_zero_cells(A1:A10)

and define the UDF
Option Explicit

Function ending_zero_cells(r As Range) As Long
Dim rcell As Range
Dim i As Long
i = 0
For Each rcell In r
If rcell.Value = 0 Then
i = i + 1
Else
i = 0
End If
Next rcell
ending_zero_cells = i
End Function

HTH,
Bernd

 

[Guest]

Great, works a treat...

Didn't think i would have to resort to VBA but just the same solved my prob.
Gonna think this through now to work out a similar function for the reverse.

i.e. Cells since first non-zero value appeared.

Rgds,

Bruce

 

[Aladin Akyurek]

=ROWS(A2:A10)-MATCH(2,1/(A2:A10>0))

which must be confirmed with control+shift+enter, not just with enter.

 

[bplumhoff]

Hi Bruce,

Option Explicit

Function cells_after_first_nonzero(r As Range) As Long
Dim rcell As Range
Dim i As Long
i = r.Count
For Each rcell In r
i = i - 1
If rcell.Value <> 0 Then
cells_after_first_nonzero = i
Exit Function
End If
Next rcell
cells_after_first_nonzero = i
End Function

Have fun,
Bernd

 

[Domenic]

Try...

=ROWS(A2:A10)-MATCH(TRUE,A2:A10>0,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!