• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Habit tracking spreadsheet

gauravsc

New Member
I would like to create a spreadsheet where i can track my habits. There will be following structure in spreadsheet.
1) Number: Numbering of habits
2) Habits: Name of Habits
3) Steak: Longest continued series of 'x' in cells
4) Max: Maximum steak i have achieved in past


i need formula to calculate steak and max. I have got this idea from medium.com/@hcokim/tracking-habits-with-google-sheets-3f769871da77

It is google spreadsheet but i cannot be always online, so i would like to create offline ms excel version. I will share once i complete. Thanks.Capture.JPG
 

Attachments

  • Book1.xlsx
    9.2 KB · Views: 14
Last edited:
I am confused with numbers here, ex: max value for habit 2 should be 6,
please upload sample sheet.
 
Hi,


Can you explain the logic used to calculate the Steak and Mean for all the 3 examples shown in the attached…since I don’t seem to be getting the common logic
 
Capture.JPG
Hi,


Can you explain the logic used to calculate the Steak and Mean for all the 3 examples shown in the attached…since I don’t seem to be getting the common logic


The logic is simple. Let me explain by example. Let say i am building a habit of drinking 4 glass of water daily. Now for that i am using this spreadsheet. In first column i will write no, which is just series of numbers and thus i may write 1. Now in the Habit column i will write "Drink 4 glass of water". Now the right side columns shows dates. I would mark X if i successfully done the thing which i intend to do, like drinking a water. The steak column is a count of continuous X. If i don t do the required action, i dont mark the X and steak is back to zero. Max is longest continuous series of X.

PS: New screen shot added.
 
Don’t you think the steak and max is incorrect for the last example..It should be

Steak = 1

Max = 4

Please correct me…

Check your attached file..
 
OK here you go...

Try the below for Cell C2 = LOOKUP(99,FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

For Cell D2 = MAX(FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

Both are to be acknowledged with CTRL + SHIFT + ENTER

See the attached for your reference..
 

Attachments

  • Gaurav.xlsx
    11.5 KB · Views: 18
OK here you go...

Try the below for Cell C2 = LOOKUP(99,FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

For Cell D2 = MAX(FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

Both are to be acknowledged with CTRL + SHIFT + ENTER

See the attached for your reference..
#Respect @Asheesh
please expalin Frequency part of the formula, how it works?
Many thanks
 
OK here you go...

Try the below for Cell C2 = LOOKUP(99,FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

For Cell D2 = MAX(FREQUENCY(IF(E2:T2<>"",COLUMN(E2:T2)),(E2:T2="")*COLUMN(E2:T2)))

Both are to be acknowledged with CTRL + SHIFT + ENTER

See the attached for your reference..


@Asheesh
Awesome...Perfect...Appreciate your work...Working as expected.

I am attaching spreadsheet with bit of conditional formatting with colors.

Please feel free to share/improve and make yourself better.
 

Attachments

  • Gaurav2.xlsm
    11.2 KB · Views: 41
Why is 5-Feb highlighted with Red..Can you explain the logic
It is today`s date. Just to distinguish.

I checked the spreadsheet. I have query for steak column. I need to mark the T2 column to increase the steak.

Let say today is 5th feb, i mark 'x' in 5th feb and i would like to steak to be updated. Now tomorrow it will be 6th feb. I need to mark 'x' in 6th feb to update the steak.

Right now only to increase the steak, T2 must be marked.
 
Are you looking for this...

C2 = LOOKUP(99,FREQUENCY(IF(E2:AI2<>"",COLUMN(E2:INDEX(E2:AI2,MAX((E2:AI2="x")*COLUMN(A2:AE2))))),(E2:AI2="")*COLUMN(E2:INDEX(E2:AI2,MAX((E2:AI2="x")*COLUMN(A2:AE2))))))

D2 = MAX(FREQUENCY(IF(E2:AI2<>"",COLUMN(E2:INDEX(E2:AI2,MAX((E2:AI2="x")*COLUMN(A2:AE2))))),(E2:AI2="")*COLUMN(E2:INDEX(E2:AI2,MAX((E2:AI2="x")*COLUMN(A2:AE2))))))

To be acknowledged with CTRL + SHIFT + ENTER

Drag them down as per your requirement
 
Hi,

This is an old thread but i stumbled upon it when i wanted to create an excel sheet to track my habits.

I have tried a few things to calculate Streak and Max (My sheet has C & D columns interchanged) , which did not work, need help.

62703

Thank you
Mahesh
 

Attachments

  • Habit_Log.xlsm
    12.8 KB · Views: 7
erukumk
You should open a new thread as written in
.
 
Back
Top