I’m a bit of a geek, so I enjoy playing with spreadsheets sometimes. If you’re similarly afflicted, you might be interested in something I’ve come up to track my productivity over time.
It’s easy to track your words written per day and watch the total as it gets bigger and bigger on a line chart. It’s fun, too, and I find it every encouraging to see that squiggle reach a little more skyward each day. (Except on editing days; then it can take dip. Feh.)
Wonderful as it is, the word total graph is always going to be moving up, overall. It doesn’t really tell you how effective your writing time is. I decided a good way to check that would be to keep a running average per day, based on all the writing days I’ve managed to accumulate for a specific project.
Here’s how to do that…
- Create a spreadsheet (I use MS Excel, but you can use whatever you like) with columns for Date (bet you can guess this one), Words (cumulative total), Written (words written today), Notes (for whatever comments), and Avg (for the average). It looks more or less like this:
- Set up the Written column so the word total from yesterday is subtracted from the world total for today, to give you a total for today. For example, in the illustration above, 17,651 words total for 8/10 subtracted from 18,138 words total for 8/11 give me 487 words for 8/11. You might have some in-line notes or something that keeps this from being exact, but the number will still be, as we say in the day-job world, “directionally correct.” In other words, close enough to give you an idea how you’re doing.
- Set up the Avg column so that the first cell in the formula stays the same and the second one increments when you drag or copy the formula to the cell below it. I know, that’s clear as a London fog, so let me break it down:
- Leave the first Avg cell, E2, blank, since there’s nothing to average yet.
- In E3, set the formula as “=AVERAGE($C$2:C3).”
- The $ signs keep the “C2” from turning into a “C3” and hosing your formula when you move it down to the next cell.
- The numeric value in the plain “C3” is going to increment by one when you move it copy it to the next cell down, which is what you want.
- Every day your write:
- Record your total words in Words.
- Copy or drag the formula in Written down to the current row.
- Copy or drag the formula in Avg down to the current row.
- After you’ve got a few rows of data:
- Select the data in the Date column.
- Keeping the Date data selected, also select the data in Avg. (To do this in MS Excel for Windows, you hold down the Ctrl key while selecting. If you use something different… Google it if you don’t know.)
- Using the Insert menu, select the line graph from the Charts section, pick the one you like best and click on it.
- You’re done! If all went well, you’ll get a chart that looks like this:
I can use this chart now to see how I’m doing, productivity-wise. Looks like I’ve been going pretty steadily at 5-600 words per writing day since September 2016, which is where I like to be. I can also tell that I had a higher average when school was out and I had more time in the mornings, without the chaos of getting people ready for school.
So, there you go. Go on and geek out. May your averages be high.
Please leave a comment, and maybe the Good Fairy will bring Wishie some shoes.