You fill in the blanks!

August 16, 2011 by 2 Comments

Figure 1: Neat data is not always "Analysis-Friendly" data

There are occasions when you will want to fill all empty/blank cells in a column. Often times, data is formatted in such a manner so that it is easier to read. Figure 1, for example, shows a portion of what an Accounting Report might produce for expenses over a given period of time. While it is very clean and easy to read, using this data to perform other analysis will require some work. If, for example, you wanted to use this data to show expenses by month or quarter, that would be difficult.

To make the data more “Analysis-Friendly”, then, the data needs to be converted to a “table” or “database” with records. To do so, we must make sure each row (or record) contains the expense (or similar category that is being grouped by the summary report). In our example file (attached), column B needs to contain the category for each row of data (date and amount).

Of course, we can do this the hard, manual way. We can retype the category for each subsequent row. No way! An easier way would be to copy the category heading and fill or paste in the subsequent rows. Better, but still some work.

There is actually an easier way.  Come on; of course there is! Read more of this post

Filed under Application (Excel, Access) and Process Development Tagged with , , , ,

A friend’s role as storyteller

August 5, 2011 by 3 Comments

One of my blog followers and someone I consider a great friend is going through a tough time at work. His relationship with his manager has soured over the last few months and he now finds himself in an unsettling quandary. As a friend, my initial reaction was to help solve the problem. But I know how I am when I’m down or in a funk—I don’t want anyone around me, let alone trying to cheer me up or analyze the situation—and I certainly didn’t want to make a situation worse by providing unsolicited advice. The fact of the matter is that all we can hope to do as parents, colleagues, mentors, coaches, and yes, friends; is to provide anecdotes, stories, and other recollections that we hope will shed some light on the issue. Read more of this post

Filed under Deep Thoughts and Philosophy, Family and Friends

Winners know how to (and that they must) push boulders!

July 31, 2011 by 1 Comment

Keep pushing life's "boulders" and it'll pay off!

Bill Parcells, the soon-to-be Hall of Fame football coach had a saying for almost every occasion and circumstance.  He was as knowledgeable about life as football and never missed an opportunity to share his wisdom and charisma.  One of his favorite expressions was used to explain the emotional fragility of young players.  “Confidence is born from demonstrated ability,” he’d say.  Of course, the thought is not original.  You may, in fact, have heard other variations of it.  “Once you’ve done it once, you can do it again.”  Or “You don’t know you can do it until you do it.”  However stated, no matter who says it or in what context, it’s as true and real as the world is round.

Confidence comes from evidence.  That’s obvious.  Or is it?  Doesn’t it take some confidence to produce the evidence in the first place?  Sure it does.  So what is it?  Does evidence lead to confidence or is it the other way around?  Indeed, like the chicken and egg quandary, the relationship between confidence and evidence is a confusing one. Read more of this post

Filed under Deep Thoughts and Philosophy Tagged with , , , ,

Grouping Dates with Pivot Tables

July 29, 2011 by Leave a comment

I am not a gambling man. But if I were, I’d bet a million dollars that you will never ever grasp all that Microsoft Excel has to offer.  Fine with me because that means we can learn something new just about every day.  Pivot Tables provide a perfect example.

Pivot Table Basics

I’v raved about Pivot Tables in the past , praising the power and flexibility of this Excel feature.  In literally seconds, you can slice and dice data any way you wish to see just the data you need to make informed decisions.  (See prior post on Pivot Tables)

The journey between just learning about Pivot Tables and being able to effectively implement and use them is indeed a short one.  But take just a few more steps and you’ll find additional ways to save time, while adding helpful details to your information and analysis.  Let’s say, for example your company keeps records for each sales transaction.  A simplified table might include the name of the salesperson along with the date and amount of the transaction.

Using just the basic elements of Pivot Tables, one can easily produce a table showing the total sales for each date and each salesperson. (See Illustration).  But having data laid out in this manner is not completely helpful.  A better way to group the information might be by year, quarter, or month.  Or all three.  There are, of course, many ways to add those levels of grouping when producing this pivot table.  Those new to Pivot Tables might add columns to the raw data, converting the date to columns for the month (using the month() function), year (using the year() function), and quarter (applying the roundup() function to a simple equation: monthNumber/3- see :What Quarter is It?).  Even adding one for the week wouldn’t be that difficult. Read more of this post

Filed under Application (Excel, Access) and Process Development Tagged with , , , , , ,

It takes no skill to hustle

July 19, 2011 by 1 Comment

My wife is always telling me that I’m smart. It makes me feel kind of, well, weird. I’m not sure why. I’ve accomplished too much academically to dismiss it altogether, but it’s hard to completely accept such praise. To tell you the truth, I don’t know what the heck it even means. To be smart, that is. I mean we all say it, referring to those around us as smart or bright. And I know there are tests that are supposed to effectively measure intelligence. But how many of us have actually taken one? And if we have, what exactly has it done to help us? Schools, of course, are notorious for using standardized testing to rank its pupils. But we’ve all heard about the biases inherent in some of these assessments. Furthermore, with more and more ways to prepare and study for these and similar tests, what does a good score on them really mean? That you are able to take a test and do well? That you have the means to afford private classes and tutors? Or that your parents forced you to spend your summer afternoons systematically going through math, grammar, and reading exercises from volumes and volumes of instructional books instead of playing football or riding bikes with your buddies? Whatever it means, I’m not convinced it means you are smart. You may in fact be smart–again, whatever that means–but getting a good score on a test, or getting accepted to an Ivy League School (yeah, I said it) doesn’t mean you are smart. I’d venture to say, even, that the two –being smart and academic achievement— are not nearly as correlated as we’d like to believe, again if we could even define what being smart means.

Well, when she tells me that, my answer to my wife is usually the same. It, of course, has its foundation in sports. And not only does it humbly dispute any inkling that I’m somehow this innately smart person–trust me; I know enough really smart people to think that– but it is also a firm rebuttal to every person (I love ya, but you know who you are) who has referred to my physique— the result of twenty years of hard, dedicated work— as “mostly genetic.” Like my strength and fitness, whatever I have that makes me appear to be smart, is not something with which I was necessarily born. Read more of this post

Filed under Deep Thoughts and Philosophy, Self Improvement & Motivation Tagged with ,

← Older posts

Newer posts →