I talked about writing streaks at the beginning of this series, then went on to describe how to set up a basic word count tracking spreadsheet, added improvements to the spreadsheet, created a pivot table summary, and expanded the spreadsheet with project details. In this post, I calculate a writing streak in the word count tracking spreadsheet. I'll walk you through each step of creating the formula necessary.
You can use formulas in Excel to do all sorts of calculations. It's easier than you might think. I'll walk you through creating the formula to calculate a writing streak each step. It isn't all that complex and we'll take it one step at a time. First, let's look at what the result will look like so you see what we're working to create.
If you've worked through this series of posts, you've seen this table. It's our Words table in the word count tracking spreadsheet—with an additional Streak column. You can see the results. With each day the streak count increases by one until it encounters a gap (e.g. 4/12 - 4/14), then it resets back to one. It isn't obvious, but if you have several writing sessions in the same day, the formula won't increase the streak count.
You could, of course, fill in the streak count manually by entering it yourself each time you added new word counts. But where is the fun in that? We might as well let the spreadsheet do the calculations for us. It's what spreadsheets do best.
We want the spreadsheet to calculate our writing streak for us, so we need to think through how that will work. Let's work out the concept.
The assumption here is that we're counting any word count, and we're basing our streak on writing every day.
We need to compare the date of the current entry to the previous entry. Fortunately, Excel has a function that will help us. It's the IF function.
=IF(Compare something, Show if TRUE, Show if FALSE)
This is the basis of our formula. The IF function has three parts, separated by commas. The first part compares something, in this case our dates. The next part is what Excel should show if the comparison is true, and the last part is what Excel shows if the comparison is false. Let's go through each part.
The comparison takes the date from our word count entry, subtracts one day, and compares it to the previous row (A26 in this case). If the dates match, then the comparison is TRUE. If not, FALSE.
When the comparison is TRUE, Excel uses this simple calculation. It takes the previous streak count and adds one. That's it! Not too complicated.
In the case that the comparison is FALSE, there are two cases we have to consider that came up when we thought through the calculation earlier. The previous entry date could be the same as this entry, e.g., multiple sessions in the same day. Or, it could just be a different day because there's a gap. We'll take advantage of another property of the IF function and nest a second IF function within our first. The calculation for the third part, the FALSE case, can also use other functions like IF.
In this case, we're comparing the date of our entry to the date of the previous entry. We're just not subtracting one. If it's true, that is the dates match, then our streak count is the previous streak count. We don't add one. If the dates don't match there has been a gap and we restart the streak count at one for this entry.
The completed formula is:
Assuming you're following along in your own spreadsheet, let's look at how to add this formula. On your Data tab, go to the top of the Words table. If you have any filters selected, go ahead and clear them first.
Now click in cell F1 in your heading row and type Streak and hit ENTER. Excel expands the table to include the new column. Skip the first row and in the second row, start writing the formula by typing: =IF( at that point, use the arrow keys or the mouse to select the date for this entry. It will fill in the [@Date] part for you. Continue typing the formula with -1= and use the arrow keys or mouse to select the date cell in the first row.
Excel fills in the cell value. Type a comma, then use the up arrow or mouse to select the streak count in the first row and then type +1,IF( and select the date for this row again. Type = and select the date again for the first row, type a comma and select the streak count for the first row, another comma and then 1)).
When you hit ENTER, Excel defaults to creating a calculated column. It automatically adds the formula to all of the cells in the streak column, including new rows when you add additional entries. You don't have to write the formula again! Now, each time you add an entry Excel will calculate the streak count for you. You can keep your table filtered to the current week, month, and it will still calculate the streak count. As the number increases it gathers increasing weight.
I want to start a new writing streak, so this is a helpful feature for me to add to the tracker. I mentioned in the previous post that there are still other features to add to the tracker, including visualizations and a dashboard. I'll work on those in upcoming posts. If you want to receive emails from me about new posts and other useful information for a writer with a day job (or not), go ahead and sign up for my newsletter. I'm not going to spam your inbox, my goal is simply to share useful information. You can always unsubscribe easily at any time. It's also a great way to show your interest or to ask questions.
Ryan M. Williams lives a double life as a full-time career librarian and a multi-genre writer with over twenty books. He writes across a range of genres including science fiction, fantasy, paranormal, mystery, horror, and romance. He earned a Master of Arts degree in writing popular fiction from Seton Hill University and a Master of Library and Information Science from San Jose University. His short fiction has appeared in anthologies from Pocket Books, WMG Publishing, and in On Spec Magazine.