Results 1 to 9 of 9
  1. #1
    tinkha
    Guest

    Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    I'm working on a spreadsheet to track steps taken, along with calories, miles, etc. that kind of thing.

    I'd like to add the ability to track "streaks" of over a certain number of steps per day.

    What I have currently is a dynamic named range called Steps which contains all the Step values. I also have the "certain number" in a named range called Threshold_1. There is also a named range called Blank_days_allowed. This range contains a single value which specifies the number of days with 0 steps that can occur within a streak (allowing the user to continue a streak if they forgot to track their steps on a certain day.

    What I'm trying to do is get a count of the most consecutive days over the threshold, while allowing a certain number of 0-step days in the streak still.

    I'm attaching a sample that illustrates this a little more clearly (I hope) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    One other restriction I have is to do this in a formula if at all possible. I'm trying to not use VBA, if possible here.

    Thanks!

    Andy

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    have you tried using the COUNTIF function?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    tinkha
    Guest

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    I've thought about it, but I'm not sure exactly how to set this up. I don't want the total count of values over the threshold in the entire list, just the length longest stretch of days within that list that are over (or meet the blank days criteria). Would COUNTIF be able to do that?

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    No, I don't think so. Why don't you want a VBA answer?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    If you don't mind some additional columns in the spreadsheet, I think the attached file does what you want.

    There is one column that counts the runs of zeros and another that counts the runs over the limit. Then there's a third that combines these to the streak, allowing for two zeroes.
    Attached Files Attached Files

  6. #6
    tinkha
    Guest

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    Mostly because some of the people who'll be using this wouldn't know Excel from a hole in the ground. I don't want them seeing the macros may be viruses warning and panicing (and yes, there are ways around that, some of which were recommended by people on my other question for this sheet. I haven't had a chance to look into these much, but since this was the last piece of the spreadsheet that I need to get working, I figured I'd go for a non-VBA solution first if possible. It looks like I'm probably going to have to look into those methods though and pull together something in VBA (which I'm reasonably confident that I can write)).

    Thanks for your help!

    Andy

  7. #7
    tinkha
    Guest

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    Cool! Is there any way that this could be setup for an unknown amount of data (i.e., not copying the formulas down to some arbitrary record number)? My ideal solution would not limit records at all, and stay tiny file size wise (so pasting all the way down to row 65536 isn't an option, unfortunately). I know I'm pushing my luck here <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Andy

    Thank you for your help!

    Andy

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    Nicely done Ian, <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

    I don't think you can extend my solution to an arbitrary number of rows without using VBA, since the entries have to get into the additional columns somehow.
    More experienced heads may have an answer, however.

    Looking back, I realise that you wanted to allow the number of blank days to be a variable. I've attached an updated version that uses the OFFSET function to do this.

    Ian.
    Attached Files Attached Files

Posting Permissions

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