5 Years of Continuous Blogging, Part 4

This article is the fourth (and final) one in my series about having blogged continuously for five years. I discuss the process of collecting my blogging data and reporting them in tables and graphs, but this time, the process differs from the quartile methodology that I cover in Part 2. That article emphasizes spread of word counts, and this article emphasizes high/low word counts and distribution over 10 word-count intervals.

The top pixstrip shows a table with the basic information that I put in Excel for creating the column graph. In the table, the first column lists the word count divisions, and the second column lists the number of articles for each word count category. In the graph, the word count divisions are in the x axis, and the numbers of articles are in the y axis, represented by the blue 3D columns.

The distribution and percentages of articles show that the largest group of my articles ranged from 400 to 499 words—27, a smidge more than 20%. Combined with the other three largest groups (range from 400 to 799 words), my articles totaled 82, 63.1% of the 130 articles over five years time, from September 2009 through August 2014.

In the three previous articles that I wrote about blogging for five years, I listed various steps for collecting information and generating visual results. For bloggers and other interested parties who might want to create your own similar info, the basic steps are as follows (also noted in Part 3 of my series):

1. In Excel, log the article titles.
2. Open each article, and copy/paste verbiage into MS Word, noting the word count at Word's lower left corner.
3. In Excel, copy the word count number into the cell adjacent the appropriate article title.

You might want to insert empty rows to separate significant time periods. You might even copy the set to another spot before putting in separators.

The following pixstrip shows the Excel window for sorting, and the partial image of the Period A sorted table. The Excel window opens after clicking Data tab > Sort and clicking Add Level for obtaining a second sort criterion. At the table, I added reddish outlines at the highest and lowest word counts.

The following image shows high and low word counts for periods A through E, with red outlines at the absolute lowest and highest word count numbers.

It might seem odd that I place the graphing process pixstrip before the other images. Usually, my images are sequential. For this article, the results image is more eye-catching at the very top. FYI, my tools in my series have been Excel, MS Word, Windows 7 Snipping Tool, and my ancient PaintShopPro 7.04.

---

Links to the series

1. 5 Years of Continuous Blogging, Part 1
   Focus on single- and five-year views for total articles, articles with images, and recipe articles.
2. 5 Years of Continuous Blogging, Part 2
   Focus on numbers of words in articles and graphical representations.
3. 5 Years of Continuous Blogging, Part 3
   More details on collecting the data.
4. 5 Years of Continuous Blogging, Part 4
   Emphasis on data sorting and distribution of word count groupings.

5 Years of Continuous Blogging, Part 3

This article is the third one in my series about having blogged continuously for five years. It describes how I collected the basic data for my five years of continuous blogging, used some elementary Excel calculations, and graphed some results.

The pixstrip shows a truncated Excel tally sheet and a partial Word-paste window. The Excel portion has a red rectangle around the recipe article that I did the word count on. The partial Word paste shows truncation of the recipe text. The lower left of the Word window displays the word count without needing to open menu options or use shortcut keys. How convenient!

Creating a spreadsheet with initial data
I tiled the Word file of my catalog next to a browser view of my blog site to create the following columns in Excel:

1. Article titles ("Article Title")
2. Word count ("# Words")—more on that farther down the article
3. Image, y/n ("Image, 0 or 1")
4. Recipe, y/n ("recipe, 0 or 1")

I differentiated the time periods (September to August over five years) by changing font colors. If you choose to try assessing your own blog over years or other time periods, you can try differentiating by text color, bolding, italics, border looks, etc. (For recipes, I flood-filling rows for quick visual cues.)

In retrospect, the catalog was helpful for quickly confirming recipe status, but not crucial. The most important compilation tool was the index in the blog. It grouped by years and months, and linked to all the articles.

Although I created a column for word count early on, I collected the word counts after creating the rest of the spreadsheet. It seemed more sensible to do word count as a separate process than going through each cell of each row. The word count process follows.

EZ Word Counting
For multiple article word tallying, it was helpful using a wide-screen monitor for tiling my blogsite on one side, the Excel tally sheet on opposite side, and MS Word somewhere else. I performed the following steps:

1. Opened the blog article webpage.
2. Selected the content—excluding title, superfluous info, images, keywords, and reader comments, and pasted it into the clipboard (Ctrl+C).
3. Opened Word, pasted the clipboard contents onto the page (Ctrl+V).
4. Noted the word count at the bottom left of Word window, and typed it into the appropriate Excel cell for word count.

I repeated the process of opening a blog article, copying, pasting into Word, and entering the word count into Excel. However, when bringing Word back to the foreground, I selected the existing text and pasted the new text in its place. I continued with this copy/paste process till finished.

Using Some Very Basic Excel Commands
Using formulas is not necessary in the info gathering, but interesting to calculate totals, averages, highest and lowest word counts (endpoints), and medians (midpoints). Creating graphs require two main layouts of info in Excel—1) calculated values in cells in grid form and 2) and "physical" cell distribution.

The only formula that is necessary for creating the graphs for contrasting numbers of articles over n years is summing. In the Excel window, select the cells, and select the summing icon—
Formula tab > Autosum icon.

Expanding the icon displays options for Sum, Average, Count Numbers, Max, Min, and More Functions. Besides using the summation formula for obtaining the data for creating the graphs in Part 1 of my article series, I also used the percentage formula for creating the table.

Note: I copied some of the data and placed them in another clear area so I could select adjacent cells and use graphing features Insert tab > Chart > 3-D Clustered Column and Chart > Line, respectively. Right-clicking in various areas provided additional options, such as displaying data labels, and modifying titles and other labels.

In case you are a blogger and have not yet tried collecting info on your own articles but might want to try—well, the tools are in the three parts I've written. Click "1" and "2" for the first two articles. For "5 Years Continuous Blogging, Part 4", I will share article links and stats that resulted from my trip down memory lane, my memory lane that I created for myself.

---

October 31, 2014: Links to the series

1. 5 Years of Continuous Blogging, Part 1
   Focus on single- and five-year views for total articles, articles with images, and recipe articles.
2. 5 Years of Continuous Blogging, Part 2
   Focus on numbers of words in articles and graphical representations.
3. 5 Years of Continuous Blogging, Part 3
   More details on collecting the data.
4. 5 Years of Continuous Blogging, Part 4
   Emphasis on data sorting and distribution of word count groupings.

5 Years of Continuous Blogging, Part 2

In my article about blogging 5 years (part 1), I emphasize quantities of articles over the last five years. This time, I emphasize numbers of words for articles. The images indicate distribution in scatter plots, a line chart, and a box-and-whiskers chart.

The scatter plot for the third year indicates a narrower stream of word counts than the other years. However, it's also the year with the fewest articles. The spread between high and low for word count is as follows:

Sep 2009 to Aug 2010	1323
Sep 2010 to Aug 2011	895
Sep 2011 to Aug 2012	613
Sep 2012 to Aug 2013	1277
Sep 2013 to Aug 2014	852

The line chart shows data points for high, average (mean), median, and low word counts. The box-and-whiskers chart displays the bunching of data. The "whiskers" in the box-and-whiskers graph display the end points to the "box". For each unit:

• The box represents the middle 50% of the word-count range.
• The whiskers each depict the other 50%—25% at each end.
• The line inside the box depicts the median of the word count (spreadsheet-sort of word counts by article and establishing the midpoint).

For each year's period, the line chart numbers for most words, fewest words, and medians coincide with end points and medians in the box-and-whiskers chart. Note that the fifth year "box" (smallest of the five year periods) shows that half the articles fall between approximately 400 and 600 words, with the median around 450.

Some handy resources on graphing, with the first two being the most helpful for me:

• Scatter Plots in Excel
• How to Create a Box and Whisker Plot in Excel 2010
• Box and whiskers plot
• Standard Deviation Calculator
• Handy Dummies website for visual reinforcement: Box-and-Whisker Charts for Excel

Excerpt from Box plot that summarizes "whiskers":

lines extending vertically from the boxes (whiskers) indicating variability outside the upper and lower quartiles

For "5 Years Continuous Blogging, Part 3", I will go into more detail about the road I traveled in obtaining and processing my data.

---

October 31, 2014: Links to the series

1. 5 Years of Continuous Blogging, Part 1
   Focus on single- and five-year views for total articles, articles with images, and recipe articles.
2. 5 Years of Continuous Blogging, Part 2
   Focus on numbers of words in articles and graphical representations.
3. 5 Years of Continuous Blogging, Part 3
   More details on collecting the data.
4. 5 Years of Continuous Blogging, Part 4
   Emphasis on data sorting and distribution of word count groupings.

5 Years of Continuous Blogging, Part 1

I initially started blogging to create writing samples in addition to samples I have for my technical writing portfolio. Every month for the past 5 years, starting in September 2009, I have published something. My monthly count has been as few as one and as many as five. I know the quantity does not come anywhere close to many prolific bloggers, but I know of many bloggers who were sporadic or stopped.

For me, a motivator for publishing was imposing a schedule for myself. Initially,I posted three times a month. During one contract writing/editing job, I cut the posting to once a month throughout 2011. Since then, except for a 16-episode of weekly century plant posts in 2013, I have posted twice a month.

Not sure I'd return to more often if and when I again exit the working world. In any case, when I publish, the motions remind me of the clock reset (countdown timer) in the Lost TV show.

My graphs reflect the stats for the 5 periods for September through August (2009 through 2014). Wanting to generate stats, formulas, and graphs sent me scurrying to some Excel how-to Google searches. (I hardly ever use Excel.) Was I rusty! Couldn't remember how to assign repeating rows, create a formula, or generate a graph! Conveniently, during my research over my 130 articles, I ran across my SurveyMonkey article, which I had foresight to list some Excel formulas.

A couple of handy Excel-info webpages:

• How to Add Header Row in Excel
• How to Create a Graph in Excel

The images show 5 years for total posts, posts with images, and recipe posts. Although the 3D graph is cute for the depth looks, I feel the line graph actually shows better contrasts among year-to-year and also type-to-type trends.

I had a bit of fun playing with Excel in creating the tables and graphs, then playing with them in my ancient PaintShopPro 7.04. In Excel, I became reacquainted with inserting some basic formulas and creating graphs. I rediscovered graphing options for type (bar, line), inserting numbers and titles, and moving numbers. In PSP, I further played with moving pieces, cropping areas, and combining the charts.

Related posts, besides the one about SurveyMonkey and using Excel:

• My Blog Article Methodology Inception2finish
• Pixstrips in Blog Articles

For "5 Years Continuous Blogging, Part 2", I will focus on word counts and number spreads.

---

October 31, 2014: Links to the series

1. 5 Years of Continuous Blogging, Part 1
   Focus on single- and five-year views for total articles, articles with images, and recipe articles.
2. 5 Years of Continuous Blogging, Part 2
   Focus on numbers of words in articles and graphical representations.
3. 5 Years of Continuous Blogging, Part 3
   More details on collecting the data.
4. 5 Years of Continuous Blogging, Part 4
   Emphasis on data sorting and distribution of word count groupings.