Writing 365 Tracking - Behind the scenes, Part 1

To help with my project of writing 1,000 words per day in 2016, I use a Microsoft workbook to track progress. In this post you will see how I created the various measurements.

In an earlier post I described the Writing 365 project. With an end goal of writing 365,000 this year, my daily goal has to be a minimum of 1,000 words per day.

I created a Microsoft Excel workbook to track my progress. Using four basic data points, each with its own column, I have created everything you see below.

Layout of the workbook

Microsoft Excel provides the ability to have multiple worksheets, also known as tabs. For this project, I have four worksheets: Summary, Log, DailyCount, and Lookup.

Log

The four fields I populate with data are the week, date, category of writing, and number of words. Each week starts on a Friday and ends on a Thursday. The date is fairly self-evident. The four categories of writing are devotional, journal, blog post, and e-book. I enter in the number of words as provided by Apple Pages (devotional and journal), Microsoft Word (e-books), and Mozilla Firefox (blog posts) using the Word Count Tool add-on.

VLOOKUP
When I type in the date, the week number is automatically populated using a VLOOKUP formula. This is a convenient way to keep a table of related data and pull various bits of information from it. To use VLOOKUP, you first need a table of data. In my case, this is a simple table located on the Lookup worksheet.

The formula reads: =VLOOKUP(B2,Lookup!A:B,2,FALSE)

What it means

• Find the value in cell B2 (from my Log worksheet).
• Take this value and find it in Column A in the lookup table.
• When the matching value is found place the value from the second column (Column B) into cell A2 (Week) of the Log worksheet.

CONDITIONAL FORMATTING
For the Words column, I use the solid data bars from the Conditional Formatting options. Conditional formatting is an easy way to highlight cell values in comparison with one another or to highlight the highest or lowest number.

DailyCount

Using the SUMIF formula, I total the number of words per day and use this information to update a chart of my progress.

I entered a date (column A) for each the the 366 days in 2016 (this is a leap year). Using the Date, in column B, I wrote a formula to total the number of words for each of the corresponding entries on the Log worksheet. Typically, there are four entries (journal, devotional, e-book, and blog post) for each date on the Log worksheets. The SUMIF formula totals numbers if they meet certain criteria.

The formula reads: =SUMIF(Log!B:B,DailyCount!A3,Log!D:D)

What it means

• Look at the entire column B (Date) on the Log worksheet and find values that match the values on the DailyCount worksheet (column A).
• For any matching values, total the numbers.

DAILY COUNT CHART
Using the Excel chart wizard, I created a line chart with the number of words per day and my daily goal (1,000) words per day.

These simple tools help to analyze my progress and visual the results. In the Part II, I will discuss the Summary worksheet.