+ Reply to Thread
Results 1 to 21 of 21

Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.

    Thank you in advance!

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Intalzky

    Assuming your list starts in A2, then in any cell on row 2 type
    Please Login or Register  to view this content.
    and copy down as far as necessary.

    Let me know if this is OK for you

    Regards
    Alastair

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi aydeegee, thank you for your Quick response. To give you a better understanding regarding my problem please refer below.

    Capture.PNG

    Column A would be the Names of Agents.
    Column B:1 to K:1 would be number of days.
    And below the days are their percentage. What I would like to do is to count the number of consecutive "0%" in Column M.
    However, here is the catch. for example Agent 1 already have 4 consecutive "0%" from Day 1 to Day 6, but on Day 7 Agent 1 incurred 5%. the count will be reset and start again to count starting from the next "0%" Day. (Day 8 on the example). So Agent 1 have 3 days consecutive "0%".

    And just to add, I would like also to exclude the blank cells in the count. Because blank cells means that agents are OFF from work during that day.

    I really hope you can help me with this.

    Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Intalzky

    I am unable to get access to Excel at present - serious hardware issues.

    I will get back to you as soon as I can, but anyone else should feel free to assist.

    Regards
    Alastair

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Intalzky

    Here a solution. It uses a macro - so if you have not used macros before, you may have to adjust your Excel security level.

    Click on the blue button to run the macro. To see the macro press Alt+F8 / Step Into

    Regards
    Alastair
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Alastair, This perfectly works. However using macro is the last that I can think of because this is a running file. Number of days will be added as the days go by and so will for the number of agents. What I'm really preferring is an excel formula to use.

    But if macro is the only way to do this. Is there as way to adjust the columns and rows if I'll be adding data? Can you please teach me as I'm not that good in terms of VB codes.

    Thank you so much

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Intalzky

    The macro already worked for as many rows as necessary, it now caters for as many columns as necessary.

    It may be possible to do it with a formula, but I am not clever enough to work it out.

    I have annotated the macro, so that you may better understand what is going on. Please note that I am not claiming that this is good programming, or anything - just that it works

    Regards
    Alastair
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Alastair. This is awesome!!! Thank you so much Last Favor, is it possible to set a conditional format on the answers? I want to highlight in green the cell with Answer equal to 45 and above.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    =IF(ISNA(LOOKUP(2,1/(B2:K2<>0))),COUNT(B2:K2),COUNTIF(INDEX(2:2,LOOKUP(2,1/(B2:K2<>0),COLUMN(A2:J2))+2):K2,0)) gives those results
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2011
    Location
    Eastbourne, England
    MS-Off Ver
    Excel 2010
    Posts
    1,089

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Martin - Nice one!

    Intalzky

    Martin's solution is what you originally asked for, but in case you still want the conditional formatting, here it is.

    Regards
    Alastair
    Attached Files Attached Files

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,129

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Same result with frequency:

    =IFERROR(INDEX(FREQUENCY(IF(B2:K2="","",COLUMN(B2:K2)-COLUMN(B2)+1),LOOKUP(2,1/(B2:K2<>0),COLUMN(B2:K2)-COLUMN(B2)+1)),2),COUNT(B2:K2))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  12. #12
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Thank you everyone! You're all awesome!

    Alastair's Macro and Martin's formula works perfectly..

    Hi bebo,

    Thanks for your response but I think something is missing in the formula. as it counts the all % including non zero cells
    Last edited by Intalzky; 03-15-2014 at 01:20 AM.

  13. #13
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Alastair,

    I came up with a new scenario using the template with macro. I really hope you can help me with this.

    When the count already reaches 45, can the count be reset back to zero?
    For example, Agent 1 already have 45 consecutive 0% can the count be reset starting from the next day Agent 1 have 0% again and be reset again for the next time Agent 1 reaches another 45 consecutive 0% again and so on.

    And if possible, on the next sheet. I wanted to see the Dates where Agents reaches the 45 count.

    Example:

    1st 2nd 3rd
    Agent 1 March 1 April 15 May 30 and so on.

    Thank you so much in advance!

  14. #14
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    to count Zeros you could also use
    copy paste the formula then hold control and shift together and hit enter to make it array formula

    =SUM(--((COLUMN(B2:K2)>MAX(IF(B2:K2>0,COLUMN(B2:K2),0)))*(B2:K2<>"")))
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  15. #15
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Hemesh,

    Your formula for counting consecutive zeros is working. however, i just came up with a new scenario that if the count reaches 45, the count will be reset back to zero by the next day.

    And also if there's a formula to get the DATE where the agent got the 45th consecutive 0.00%

    I really hope you can help me with this.

    thank you in advance!

  16. #16
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    can you please upload a sample showing what you are trying to achieve. I think you will need a helper column.
    Last edited by hemesh; 05-01-2014 at 02:54 PM.

  17. #17
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Hemesh,

    I have attached a sample file for your reference. Raw Data is in Sheet 1 and formula that I need to work is in Sheet 2.

    Thank you so much in advance!
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hello Intalzky,
    This formula will work till data in HQ column if data range expands then change references in the formula about which I have written in attached book
    try below in D2 in Sheet 2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    above formula is array entered

    drag down and drag to right

    Hope this helps
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Hi Hemesh,

    Thank you for checking on this. The formula almost work but I think its still missing something.

    For example on Agent 107, this agent incurred 45 consecutive 0.00% on 04/11/14. but the formula is showing blank.

  20. #20
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    Quote Originally Posted by Intalzky View Post
    Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.

    Thank you in advance!
    for Agent 107 the consecutive 0 after non zero value is only 3 so formula is showing blank. If you make Cell BU107 to 0 instead of 3.33 then you will get your answer
    Last edited by hemesh; 05-02-2014 at 06:38 AM.

  21. #21
    Registered User
    Join Date
    03-13-2014
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

    That is correct, that is why the formula on column B should be revised. Because still Agent 107 incurred a 45 consecutive 0.

    If we can have the formula count the 45 consecutive 0, then start counting again on the next day with 0 value.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] MIN value excluding blanks
    By coach.32 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-19-2013, 04:52 PM
  2. [SOLVED] Average for non-consecutive cells, excluding zero values.
    By musik7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2013, 06:11 PM
  3. Count row excluding cell
    By Traveleravi in forum Excel General
    Replies: 5
    Last Post: 04-18-2012, 04:31 AM
  4. Count IF excluding blanks or zeroes
    By Ash in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-02-2006, 07:45 PM
  5. [SOLVED] Average, Excluding Zeros, Non-Consecutive Range
    By Coal Miner in forum Excel General
    Replies: 9
    Last Post: 08-04-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1