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

1. 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. Re: Counting consecutive non-zero values in an array (Excel 2000 SR-1a)

have you tried using the COUNTIF function?

3. 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. 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?

5. 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.

6. 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)).

Andy

7. 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

Andy

8. 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>.

9. 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.