How to calculate the current streak in a habit tracker.
Amreen is building a habit tracker in Excel. For each goal, he wants to report the current streak.
I show how to use XMATCH to solve this problem.
Table of Contents()
(0:00) The goal
(0:40) XMATCH formula for last 1
(1:08) Last zero
(1:29) No streak?
(1:45) How long is streak?
(2:30) Episode 735 for longest streak
(3:05) Converting to checkmarks
(4:32) Colors in Excel number format
Amreen is building a habit tracker in Excel. For each goal, he wants to report the current streak.
I show how to use XMATCH to solve this problem.
Table of Contents()
(0:00) The goal
(0:40) XMATCH formula for last 1
(1:08) Last zero
(1:29) No streak?
(1:45) How long is streak?
(2:30) Episode 735 for longest streak
(3:05) Converting to checkmarks
(4:32) Colors in Excel number format
Transcript of the video:
Calculate the current streak in a habit tracker.
I love this question and this idea from Amreen. Sorry, I don't know how to pronounce that.
He's building a habit tracker tracking his habits over 30 days and looking for the current streak.
How many days in a row has he done some particular item?
What a great idea this is. So here's how I'm going to build this.
The numbers one through 30 going across the top, things I want to do going down the left hand side.
If I did something that day, I put a one. If I didn't do it that day, I put a zero.
We want to figure out here at the end how many ones in a row do we have.
The formula I'm going to use to find the last one is the XMATCH formula.
I'm looking for a one. Where am I looking?
I'm looking in these 30 cells right here. I'll press...
I want to look down the column, so I'm going to press F4 three times.
One, two, three times, and I want an exact match, so a zero.
Ah, this is really cool.
XLOOKUP and XMATCH have the ability to search last to first to find the last item in the list, which is great.
So the last one occurred on the 25th. I'll take that formula and we'll put it in here.
This time we're looking for a zero. Where is the last zero?
All right, so yep, good. Now this one, perform a seminar or webinar.
Today's Sunday. I'm not doing one today.
So there is currently no streak, so we're going to handle that first.
=IF the last zero is greater than the last 1, then "No streak! Try today".
A little bit of motivation there, right?
And if that's not true, that means that we're currently in a streak.
And that streak is going to be where is the last 1 minus where is the last zero.
And so it says that our current streak here for the Magic Mind Energy Drink is a 6-day streak. One, two, three, four, five, six.
If I do it again tomorrow it'll grow to 7, and then if I don't do it on Tuesday then it resets the streak, like that.
Publish a video, I'm currently on a 1-day streak, assuming that this video gets published today.
Perform a seminar or a webinar, currently not in a streak, but let's say that I think I have one scheduled on Wednesday, right, so then I'll be on a 1-day streak like that.
I'm going to point out that this isn't the first time I've dealt with streaks.
All the way back in episode 735, it was how to calculate the longest win streak.
And in that particular case it was something like: If I had put together a streak like this and then missed a day.
And then a short streak like that, it would be able to find that longest winning streak.
So that's not what we're doing today.
That's already been addressed here, go back to episode 735...
I'll put an I in the top right hand corner to go back to that.
Now Amreen said that he wants to have checkboxes, right? Zeros and ones are much easier to deal with.
But we can make it look like it's checkboxes, right.
We essentially have three states here, a zero, a one, and nothing at all for the days that are in the future. Right, so my goal is to make it look like this.
How do I find those checkboxes?
I put the numbers one to 255 down the left-hand column and then =CHAR of that cell.
In Webdings, Wingdings, Wingdings2 and Wingdings3 and Calibri.
Once I find the checkbox I want to use, I'm going to use this one, we know that Q is unchecked, R is checked, All right. I can never remember that.
I ended up building that little worksheet. In fact, here, I built that worksheet today.
Just because I can never remember where they are or which one it is.
And sure, you might be using emoticons or something to make it easier, right.
So we select all 30 cells, all of the habits we want to track.
We'll press Control 1, we'll go into the Custom Number Format.
There's four zones here.
The first zone is positive, then negative, then zero, then text. Each zone is separated by a semicolon.
Let's see, I want to put in R for the positive numbers, so R.
For negative numbers, that should never happen, so nothing there.
Two semicolons in a row.
And then Q for zero and then for text, nothing, right. So we have...
that should give us a series of Rs and Qs, and that's working.
Sometimes the letter that you type in there, you need to put a backslash R before it.
And here they actually did that for me, all right.
So for positive numbers is green and for negative numbers, in square brackets, red, like that.
Yes. All right, good.
So red Qs and green Rs. We're almost home.
Just come here and change the font from Calibri to Wingdings2.
And now we get our little checkboxes.
And tomorrow, if I put a one in there, I get a new checkbox. If I put a zero in there, we get the X.
Right, so it looks like we want it to look, and hopefully that's good.
I suppose you could hide these two columns or even improve that formula over there in AH2, embed these... Whichever you want to do.
This though should get you going, all right. What a great motivation tool here in Excel.
Great idea from Amreen.
Hopefully these steps, while convoluted, will get you there. And Amreen, look down in the YouTube comments.
Give it about a day, there'll be better ways to do this.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I love this question and this idea from Amreen. Sorry, I don't know how to pronounce that.
He's building a habit tracker tracking his habits over 30 days and looking for the current streak.
How many days in a row has he done some particular item?
What a great idea this is. So here's how I'm going to build this.
The numbers one through 30 going across the top, things I want to do going down the left hand side.
If I did something that day, I put a one. If I didn't do it that day, I put a zero.
We want to figure out here at the end how many ones in a row do we have.
The formula I'm going to use to find the last one is the XMATCH formula.
I'm looking for a one. Where am I looking?
I'm looking in these 30 cells right here. I'll press...
I want to look down the column, so I'm going to press F4 three times.
One, two, three times, and I want an exact match, so a zero.
Ah, this is really cool.
XLOOKUP and XMATCH have the ability to search last to first to find the last item in the list, which is great.
So the last one occurred on the 25th. I'll take that formula and we'll put it in here.
This time we're looking for a zero. Where is the last zero?
All right, so yep, good. Now this one, perform a seminar or webinar.
Today's Sunday. I'm not doing one today.
So there is currently no streak, so we're going to handle that first.
=IF the last zero is greater than the last 1, then "No streak! Try today".
A little bit of motivation there, right?
And if that's not true, that means that we're currently in a streak.
And that streak is going to be where is the last 1 minus where is the last zero.
And so it says that our current streak here for the Magic Mind Energy Drink is a 6-day streak. One, two, three, four, five, six.
If I do it again tomorrow it'll grow to 7, and then if I don't do it on Tuesday then it resets the streak, like that.
Publish a video, I'm currently on a 1-day streak, assuming that this video gets published today.
Perform a seminar or a webinar, currently not in a streak, but let's say that I think I have one scheduled on Wednesday, right, so then I'll be on a 1-day streak like that.
I'm going to point out that this isn't the first time I've dealt with streaks.
All the way back in episode 735, it was how to calculate the longest win streak.
And in that particular case it was something like: If I had put together a streak like this and then missed a day.
And then a short streak like that, it would be able to find that longest winning streak.
So that's not what we're doing today.
That's already been addressed here, go back to episode 735...
I'll put an I in the top right hand corner to go back to that.
Now Amreen said that he wants to have checkboxes, right? Zeros and ones are much easier to deal with.
But we can make it look like it's checkboxes, right.
We essentially have three states here, a zero, a one, and nothing at all for the days that are in the future. Right, so my goal is to make it look like this.
How do I find those checkboxes?
I put the numbers one to 255 down the left-hand column and then =CHAR of that cell.
In Webdings, Wingdings, Wingdings2 and Wingdings3 and Calibri.
Once I find the checkbox I want to use, I'm going to use this one, we know that Q is unchecked, R is checked, All right. I can never remember that.
I ended up building that little worksheet. In fact, here, I built that worksheet today.
Just because I can never remember where they are or which one it is.
And sure, you might be using emoticons or something to make it easier, right.
So we select all 30 cells, all of the habits we want to track.
We'll press Control 1, we'll go into the Custom Number Format.
There's four zones here.
The first zone is positive, then negative, then zero, then text. Each zone is separated by a semicolon.
Let's see, I want to put in R for the positive numbers, so R.
For negative numbers, that should never happen, so nothing there.
Two semicolons in a row.
And then Q for zero and then for text, nothing, right. So we have...
that should give us a series of Rs and Qs, and that's working.
Sometimes the letter that you type in there, you need to put a backslash R before it.
And here they actually did that for me, all right.
So for positive numbers is green and for negative numbers, in square brackets, red, like that.
Yes. All right, good.
So red Qs and green Rs. We're almost home.
Just come here and change the font from Calibri to Wingdings2.
And now we get our little checkboxes.
And tomorrow, if I put a one in there, I get a new checkbox. If I put a zero in there, we get the X.
Right, so it looks like we want it to look, and hopefully that's good.
I suppose you could hide these two columns or even improve that formula over there in AH2, embed these... Whichever you want to do.
This though should get you going, all right. What a great motivation tool here in Excel.
Great idea from Amreen.
Hopefully these steps, while convoluted, will get you there. And Amreen, look down in the YouTube comments.
Give it about a day, there'll be better ways to do this.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
