Addiction/Habit-Changing Sheet Current, Longest, Streaks of Consecutive Values

I have created a workbook (attached) for tracking progress at habit cessation. Column A holds the various statistics and data reported to the user. Column C is just a list of consecutive dates (input start date in C1, then just used +1 & fill down - this is only informational for the user). In Column B, the user simply inputs the number of times he engaged in the activity on the date (Column C). What I want to report are: The Longest streak of 0 values, excluding blank values, and the current streak of 0 values, excluding blank values.

I have a functioning formula for longest streak:
=MAX(FREQUENCY(IF(B:B<>"",IF(B:B<1,ROW(B:B))),IF(B:B>0,ROW(BB))))

I have been able to find formulae for winning streaks, but have not been able to edit them to suit this purpose. If possible, I would also like to be able to automatically report, in descending order of importance:

• Number of streaks equal to, or greater than, a given value.
• A list of all the streaks of 0 values
• Date of last greater than 0 value
• Date range of the longest streak, and other streaks (e.g. Longest Streak: 112 days from January 3rd, 2015-

Currently, the book is set up to work for a year. It has 13 sheets: 1 for Continual tracking, and one for each month. I would like to be able to remove the continual tracking sheet, and allow the user to input into the monthly sheets only. So, the book would have to treat columns B from all sheets as a continuous string.

Column A's Data and Statistics:

• Opportunities per Day - user inputs average number of times per day he used to engage in the behaviour
• Opportunities to Date - Number of opportunities within the sheet's interval (A2*Count of B:B)
• Total Opportunities - On the Monthly sheets, number of days in the month (Count of C:C) altogether multiplied by Opp. Per Day
• Wins - Number of times you have not engaged in the behaviour in a given opportunity. If Opportunities per day is set to 4, a 0 entry will give you 4 wins, 1 will give you 3 wins, 3 will give you 1 win, and 4 or >4 will give you 0 wins. It's set not to allow negative values - you cannot lose wins as you go.
• Losses - Opposite of Wins. Will accrue no more losses, per day, than "opportunities per day" value. E.g. Opp/Day=4, you engage in behaviour 12 times on a date, you get 4 losses, not 12.
• Success Rate - Wins/Opportunities to date, Cell Type is Percent
• Current Streak - This is the most important one to figure out! I want it to count the number of consecutive 0 values at the bottom of Column B.
• Longest Streak - Longest consecutive run of 0 values in Column B
• Streak Goal (Continuous Sheet) - Current goal for consecutive days without engaging in the behaviour
• Current/Goal (Continuous Sheet) - Current Streak value/Steak Goal value, percent cell. Informs you of progress toward goal, by percentage of completion.
• Longest/Goal (Continuous Sheet) - Expresses your longest streak as a percentage of your current goal.
• Current/Longest - Expresses your current streak as a percentage of your longest streak.
• Percent Complete (Monthly Sheets) - How much of the month is over, as a percentage.
• Days Left (Monthly Sheets) - How many days remaining in the month.

So, the user only has to enter Opportunities/Day in A2 (set to 1 as default), the start date in C1 on the Continuous sheet, or if he wants to change the intervals, and Streak Goal, if interested in that measurement, and then just each day's data.

Hi

Welcome to forum!

A14

Formula:

Please Login or Register  to view this content.

Enter