Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
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 " wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
=ROWS(A2:A10)-MATCH(2,1/(A2:A100))
which must be confirmed with control+shift+enter, not just with enter. Bruce wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count cells since last non-zero value
Try...
=ROWS(A2:A10)-MATCH(TRUE,A2:A100,0) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Bruce" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count colored cells? | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions |