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

#### Attachments

• 9.2 KB Views: 13
Last edited:

#### Ashhu

##### Active Member
I am confused with numbers here, ex: max value for habit 2 should be 6,

#### gauravsc

##### New Member
I am confused with numbers here, ex: max value for habit 2 should be 6,
Updated post

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

#### Asheesh

##### Excel Ninja
Don’t you think the steak and max is incorrect for the last example..It should be

Steak = 1

Max = 4

#### gauravsc

##### New Member
Don’t you think the steak and max is incorrect for the last example..It should be

Steak = 1

Max = 4

You are right....

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

• 11.5 KB Views: 16

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

#### Ashhu

##### Active Member
@Asheesh I will go through same. 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

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

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

#### Attachments

• 11.2 KB Views: 37

#### Asheesh

##### Excel Ninja
Why is 5-Feb highlighted with Red..Can you explain the logic

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

Thank you
Mahesh

#### Attachments

• 12.8 KB Views: 2

#### vletm

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