Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Counting the Spans of Zeros between non-zero values  (Read 891 times)

0 Members and 1 Guest are viewing this topic.

cewolfe

    Topic Starter


    Starter

    • Experience: Experienced
    • OS: Windows 7
    Counting the Spans of Zeros between non-zero values
    « on: July 01, 2016, 09:00:37 AM »
    I have data of the monthly total of purchases by each customer. It looks something like this: 0,0,0,100,200,0,0,300,0,0,0,20,0,250,0,0. All in separate cells in a row. I want to be able to return the values of 2,3,1. I need to find a way to count the amount of zero values between the non zero values. With or without the amount of zeros following the last nonzero number is a little less important. For reference I have a formula to count to the amount of months since the first purchase and also the amount of months since the last purchase. They are the following: =COUNT(DD2:INDEX(A2:DD2,MATCH("",T(1/A2:DD2),0))) and =ROWS(A2:DD2)-MATCH(2,1/(A2:DD2>0))+108. Any Help would be greatly appreciated. Thanks PS using Excel 2010 I believe ???

    cewolfe

      Topic Starter


      Starter

      • Experience: Experienced
      • OS: Windows 7
      Re: Counting the Spans of Zeros between non-zero values
      « Reply #1 on: July 01, 2016, 12:09:53 PM »
      Try the array formula below in DL2. Confirm it with Ctrl + Shift + Enter instead of the regular Enter. Fill down through all of your rows, then fill right as far as you need to. In each column it should list the size of the gap between non-zero entries. See attached for (hopefully) more clarity:

       =IFERROR(SMALL(IF($G2:$DJ2>0,COLUMN($G2:$DJ2)),COLUMN(A:A)+1)-SMALL(IF($G2:$DJ2>0,COLUMN($G2:$DJ2)),COLUMN(A:A))-1,"")

       The results I've checked appear to hold up?

      patio

      • Moderator


      • Sage
      • Maud' Dib
      • Thanked: 1678
        • Yes
      • Experience: Beginner
      • OS: Windows 7
      Re: Counting the Spans of Zeros between non-zero values
      « Reply #2 on: July 01, 2016, 01:14:37 PM »
      Did you just answer your own question ? ?

      Hope you get it figured out though...
         
       
      " Anyone who goes to a psychiatrist should have his head examined. "

      cewolfe

        Topic Starter


        Starter

        • Experience: Experienced
        • OS: Windows 7
        Re: Counting the Spans of Zeros between non-zero values
        « Reply #3 on: July 01, 2016, 01:20:50 PM »
        Yeah... Another forum helped me out first so I just copy and pasted the answer because I wanted to share for anyone that might need it but don't care about being perfect

        patio

        • Moderator


        • Sage
        • Maud' Dib
        • Thanked: 1678
          • Yes
        • Experience: Beginner
        • OS: Windows 7
        Re: Counting the Spans of Zeros between non-zero values
        « Reply #4 on: July 01, 2016, 01:33:19 PM »
        Good idea...Welcome Aboard .
           
         
        " Anyone who goes to a psychiatrist should have his head examined. "