Habit Tracker Streak for Habits That Aren't Daily Google Sheets

crutty

New Member
Joined
Jul 15, 2019
Messages
2
I am building a habit tracker where my habits are the columns and each day is a row. Near the top, I have a 'streak' row which is meant to be how many days from today that I have successfully completed the habit. I have managed to figured out a way to calculate this field for habits that are daily. However, a couple of my habits are not daily. For example, they may be every Monday and Thursday, or only on Wednesdays, etc. If I complete say the 'every Wednesday habit' every Wednesday, I want the streak to reflect that I am doing it each time.

Additionally, I have another row that calculates overall percentage of habit completion. This one should be easier, but still might be more complicated for habits that aren't daily.

As someone who is familiar but by no means an expert at Excel, I'm not really sure where to start. Spent a couple hours googling with no success, so if you can help me out I'd greatly appreciate it!

(Note: I'm actually working this in Google Sheets so I will link an example, but I figure other than formatting the formula to be an array formula there should really be no difference.)


Here is the link. The cells in yellow are the one's I need help with! Thanks so much and let me know if I need to do anything else.

https://docs.google.com/spreadsheets/d/11m989S7Bvt7GmiRE8lFJUYtU1AbcGxOo8raqaoaz16Q/edit?usp=sharing
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

crutty

New Member
Joined
Jul 15, 2019
Messages
2
Re: Habit Tracker Streak for Habits That Aren't Daily

Just for reference, the formula I am using to calculate the streak for daily habits is (Example for cell C3):
=IFERROR(ARRAYFORMULA(MAX(IF(C5:C=TRUE,ROW(C5:C))))-ARRAYFORMULA(MAX(IF(C5:INDIRECT("C"&ARRAYFORMULA(MAX(IF(C5:C=TRUE,ROW(C5:C)))))=FALSE,ROW(C5:C),4))),0)

For the 'Overall %' row it is (Example for cell C4):
=IFERROR(COUNTIF(C5:C,TRUE)/COUNTA(C5:INDIRECT("C"&ARRAYFORMULA(MAX(IF(C5:C=TRUE,ROW(C5:C)))))))
 

Ynwaps

New Member
Joined
Sep 11, 2019
Messages
1
Re: Habit Tracker Streak for Habits That Aren't Daily

Hey crutty,
I was looking for a habit streak formula and found your thread here on google.
Anyway it worked very well so I have improved your sheet a bit:
https://docs.google.com/spreadsheets/d/1-2Oihk_aN13n6ZqEoyD0qBrlfPJtf17RpHKKNXpL438/edit?usp=sharing

The only thing that is not working yet is to calculate streaks on off gym days for example.
But the overall% is correct.

Anyway feel free to improve this sheet further. I only registered here to give back :)
Enjoy!
 

bencole123

New Member
Joined
Jan 21, 2020
Messages
1
Platform
MacOS
Hey crutty, or Ynwaps, do you know how to translate this to apple numbers? is arrayformula the same thing as Index in apple numbers? I got the overal % to work but i need the streak to convert and im having trouble.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,014
Messages
5,508,794
Members
408,694
Latest member
LightBright

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top