Count number of rows before first non-zero

first cell not zero
find first non zero value in a row excel vba
last non zero row
excel return non zero values in row
excel find first value in row greater than zero
vlookup non zero value
match if not zero
excel find not 0

I want to count the number of rows before and including the the first non-zero per species x date. I have managed to import and sort data, and can return the value of the first non-zero row per site x date, but I cannot calculate the number of rows before the first non-zero. Ecologically, this analysis is trying to determine how many surveys one would need to do (species x date) to record our focal species (values).

I have tried to use the tidyverse/dplyr environment to do this, trying summarise() and n(), with little success. Any pointers would be appreciated.

Below is an example of data that I have been trying to write this code for:

test_df <- structure(list(site = c("a", "a", "a", "a", "a", "a", 
                               "b", "b", "b", "b", "b", "b", 
                               "c", "c", "c", "c", "c", "c"), 
                      Date = structure(c(17167, 17198, 17226, 17257, 17287, 
                                         17318, 17167, 17198, 17226, 17257, 
                                         17287, 17318, 17167, 17198, 
                                         17226, 17257, 17287, 17318), 
                                       class = "Date"), values = c(0,                                                                                                                        0, 0, 3, 4, 5, 10, 11, 12, 13, 14, 15, 0, 0, 0, 0, 45, 50)), 
                 row.names = c(NA, -18L), class = "data.frame", 
                 .Names = c("site", "Date", "values"))

This is the code to return the value of the first non-zero row (by species x date):

test_df %>% 
  # Convert site to factor, so we can use complete later. 
  # We do this within group_by, because we want to operate by level of site
  group_by(site=factor(site)) %>% 
  # Remove all rows of variable if there aren't any rows with values==0
  filter(any(values==0)) %>% 
  # Remove all rows with values != 0
  filter(values != 0) %>% 
  # Keep the first row of each variable, after sorting by date
  # This gives us the first non-zero row
  arrange(Date) %>% 
  slice(1) %>% 
  # Use complete to bring back a row for any level of variable that
  # didn't start with any rows with values==0
  ungroup() %>% 
  complete(site)

Instead of the resulting table looking like this:

# A tibble: 3 x 3
  site  Date       values
  <fct> <date>      <dbl>
1 a     2017-04-01      3
2 b     NA             NA
3 c     2017-05-01     45

I want it to return a table with values indicating the number of rows before and including the first row with a non-zero, not the value of the first non-zero, as in the table above:

I.e. For site 'a', we had to survey 4 months(rows) to record our focal species for the first time, site 'b' recorded the focal species during the 1st survey, and site 'c' recorded the focal species on the 5th survey.

# A tibble: 3 x 3
  site  Date       values
  <fct> <date>      <dbl>
1 a     2017-04-01      4
2 b     2017-01-01      1
3 c     2017-05-01      5

Using:

test_df %>% 
  group_by(site) %>% 
  mutate(n = row_number()) %>% 
  filter(values != 0) %>% 
  slice(1)

gives:

# A tibble: 3 x 4
# Groups:   site [3]
  site  Date       values     n
  <chr> <date>      <dbl> <int>
1 a     2017-04-01      3     4
2 b     2017-01-01     10     1
3 c     2017-05-01     45     5

Automatically number rows - Excel - Office Support, How do I find the first non zero value in a row in Excel? Stack Overflow Public questions and answers; First 25 Users Free Count the number of rows with non-zero values in two columns in MS-Excel. 1.

A bit more verbose than Jaap. First, I define a function that counts leading zeroes and adds one. It uses the rle (Run Length Encoding) function.

count0 <- function(x){
  tmp <- rle(x)
  ifelse(!tmp$values[1], tmp$lengths[1] + 1, 1)
}

Here, I find the date of the first non-zero element, then I apply count0 to count leading zeroes.

test_df %>% 
  group_by(site) %>% 
  summarise(Date = Date[(values>0)][1],                          
            values = count0(values))

This gives the required output.

# # A tibble: 3 x 3
#   site  Date       values
#   <chr> <date>      <dbl>
# 1 a     2017-04-01      4
# 2 b     2017-01-01      1
# 3 c     2017-05-01      5

of the selected range is counted and displayed in cell H1. With the above formula, you may count the non-zero cells which including blank cells. If you want to ignoring both the zero cells and blank cells, please apply this formula: =COUNTA(A1:D10)-COUNTIF(A1:D10,"=0") , then press Enter key to get the result, see screenshot:

Another dplyr possibility:

test_df %>%
  group_by(site) %>%
  mutate(val = ifelse((values != 0 & lag(values, default = 0) == 0) | values == 0, 1, 0)) %>%
  summarise(Date = first(Date[values != 0]),
            values = sum(val))

How do you find a number in a row in Excel? Counting Zeros in a row until encounter first non-zero value There will always be a non-zero number that is positive (greater than zero). There are no text entries and no formula blanks.

1. across the range that you want to fill. Tip: If you do not see the fill handle, you may have to display it first. In row 1 the first non-zero entry is located at position 6 and for row 2 the first non-zero entry is located at position 7.

Is there a function, or combination of nested functions to count the number of zeros in a row up until the first non-zero valued cell? For Example. The cell values are all zeros (0) except for somewhere in the middle of the column the values go up to .1, .2, .3, etc, and then back down to zero again. What I am trying to do is count the rows until the first non zero cell value. I have been trying to get a Do Until loop to count, but I am not having any luck.

Is there a function, or combination of nested functions to count the number of zeros in a row up until the first non-zero valued cell? For Example  How to lookup first non-zero value and return corresponding column header in Excel? Supposing, you have a range of data, now, you want to return the column header in that row where the first non-zero value occurs as following screenshot shown, this article, I will introduce a useful formula for you to deal with this task in Excel.