• 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

Last edited:

Ashhu

Active Member
I am confused with numbers here, ex: max value for habit 2 should be 6,
please upload sample sheet.
 

Asheesh

Excel Ninja
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
 

gauravsc

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

Asheesh

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

Asheesh

Excel Ninja
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

Ashhu

Active Member
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
 

gauravsc

New Member
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

gauravsc

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

Asheesh

Excel Ninja
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
 

erukumk

New Member
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

vletm

Excel Ninja
erukumk
You should open a new thread as written in
.
 
Top