Improvement Insights Blog

I’ve tackled statistical analyses in offices, in libraries, on airplanes, in boardrooms and in my car outside a client’s office. I’ve solved the problems of businesspeople, salespeople, factory owners, friends, family and more than a few bartenders. But I never expected to solve a statistics question while sitting under a tree beside a mountain stream… and I certainly never expected to be asked to answer a question about the work of someone who died more than four hundred years earlier.

However, just such an opportunity presented itself last week… and who am I to pass up such a unique opportunity?

I had packed myself a nice lunch and driven to this little spot in the mountains to enjoy it. I had no sooner situated myself on my blanket underneath the tree when I began to hear something over the gentle burbling of the mountain stream. It sounded like two people, it sounded like they were arguing, and it sounded like they were approaching. I could just barely make out what was being said, but as they got closer I began to hear more and more.

A young man and a young woman with backpacks came into view, walking on the path that led near my picnic spot. They walked side by side, and each seemed to be passionately defending their point while not giving an inch to the other’s assertions. They were so engrossed in their discussion that they stopped walking less than 10 feet from where I was sitting without noticing me.

“Look, we’re miles from anywhere and we’ve got a long hike to go. We’re not going to solve this out here, so let’s just forget about this for now,” he said.

Exasperated, she replied “You never listen to me, you only listen to other people! I wish there were someone out here to back me up, because any idiot could see that I’m right, and you’d believe it if they said so!”

I didn’t want to interrupt, and I certainly didn’t want to get involved, but I could tell that this was going to become awkward quickly if I didn’t alert them to the fact that they weren’t alone here.

“Ahem…” I cleared my throat. The two hikers jumped, startled.

“I don’t mean to butt into anyone’s business, but it’s such a nice day. Wouldn’t you enjoy it more if you… you know… enjoyed it, instead of arguing?”

The two hikers looked at each other and then back at me. The young woman spoke first.

“Sorry about that; we were so engrossed that didn’t even see you there.”

“What was it you were arguing about?” I asked.

“It’s kind of silly… plus it’s pointless out here because there’s no way to prove one way or another,” replied the young man.

“We were arguing about Shakespeare,” she added.

“Shakespeare?” I asked, confused.

The young woman continued, “We’re English Literature majors at the college, and we were arguing about whether Shakespeare referred to himself or others more often in his sonnets.”

I stared back at them incredulously. “You’re out here in the middle of all this beauty arguing about THAT?”

They looked at each other sheepishly, then back at me. “Yeah, I suppose it’s more than kind of silly.”

“Exactly,” I said, pulling my laptop out of my bag. “Let’s settle this once and for all so you can enjoy the rest of the day.”

Once again, the couple looked at each other, this time more puzzled. “Wait… how could you possibly do that with a computer… and out here?” she asked.

“Easy,” I replied. “Just watch. Now, if I remember correctly, Shakespeare wrote 154 sonnets that can be attributed to him without question, right?” I looked up to meet the couple’s astonished gaze. “You’re not the first folks to study Shakespeare, all right? I paid attention in school… plus I used to date a Lit major back in the day.”

My phone was still getting a strong signal, so I paired my laptop to it and searched the internet for a text download of Shakespeare’s sonnets.

“Now, I’ll copy the text into an Excel document and filter out the sonnet numbers and text separators,” I said, doing exactly that. “If that’s a blanket I see in your pack, why don’t you spread it out here so you can see what I’m doing?”

The young man did so and when they sat down, I showed them this:

“You’ve got all 154 sonnets there?” he asked.

“Yep, all pasted into a single column. And now I’m going to count how many times Shakespeare used each specific word.”

“Wait,” she interjected. “That’s going to take hours, right?”

“Nope,” I replied. “Just a few seconds. I’ve got a software program that loves problems like these.”

I highlighted the column of text and selected QI Macros’ Word Count Wizard:

“But a sonnet is a 14-line poem!” he exclaimed. “With 154 sonnets, that’s, like… like…”

“…a little over 2100 lines, yes.” I said, finishing his sentence. “Each line has between 5 to 10 words in it, so that means it’s tallying the usage of between 10,000 and 20,000 words in all 154 sonnets.” I looked down at my laptop. “Ah! It’s finished.” I pivoted the screen around so they could better see the results:

“So if you look just at single words used, it looks like the top 5 most frequently used were ‘thy,’ ‘thou,”love,’ ‘me’ and ‘thee’. So three of the five – thy, thou and thee – are referring to others, while only one is referring to himself. But let’s not stop there; QI Macros also analyzed usage of two-word phrases.

“The top 5 most frequently used two-word phrases are ‘thou art,’ ‘mine eye,’ ‘mine own,’ ‘thy sweet’ and ‘thy love.’ That’s three out of five referring to others… and in fact, the next six most frequent two-word phrases are all either ‘thy’ or ‘though,’ so I’d say that’s fairly conclusive that Shakespeare referred more frequently to others than he did to himself.”

I looked up at the couple. He held his head dejectedly while she sat hearing the news proudly, so I had no trouble deducing whose side my ruling had come down on.

“Don’t take it so hard,” I told him. “Remember what Shakespeare said: ‘A fool thinks himself to be wise, but a wise man knows himself to be a fool.'”

He looked up at she and I and smiled. “Good point. But I was just wondering what use would that software be in the business world? I doubt there’s many businesses who need to count words like that.”

“Well, that’s where you’d be wrong for the second time today,” I replied. “Say you have 20 operators in a telephone call center all writing down reasons why people don’t want to buy your product… you could quickly tally up the most-cited objections.”

“Well,” the young woman said, standing up. “I feel vindicated, and I’ve found a new useful software program. Shall we continue our hike and leave this man to his lunch?”

The young man sprang up, snatching the blanket and his backpack in one fluid motion. “Thanks a lot,” he said, shaking my hand. “Even if I wasn’t right, I certainly learned something.”

If you’re interested in learning more about using the Word Count Wizard in QI Macros, click the links to learn more HERE and HERE to read about it on the QI Macros website. (One note of caution, though: Unfortunately, the Word Count tool is not functional in Excel 2016 Professional Plus, as this version of Excel does not respond to VBA code pertaining to PivotTables.)