Thursday, March 26, 2009

"Mind" -> Pivot Charts

Our guest for today is Hans Moravec. Now if you are familir with robots, you have probably heard of Moravec. We are not going to talk about robots, but we are going to use an idea of Moravec. We are going to use his paradox.

What is Moravec's paradox? The paradox, as Hans Moravec coined it, is the basic idea that conscious thought in doing things like playing checkers requires very little computing power.

Yet, what we consider as simple (walking for instance) has not been adequately followed by even the most sophisticated supercomputer.

Let's look at this a bit more. For instance, if you've ever played chess with a computer, you know exactly what I am talking about. Very early in computers, PCs were able to play a decent game of chess. Even my Palm Pilot, which is a very early PDA, plays chess pretty well. On the other hand, if you try and get a computer to drive a car, it is massive challenge that we have yet to master. We simply don't know how to get a computer to visually take in information and turn it into decision making. There is something in the human brain that is more powerful than any supercomputer, and allows us to deal with visual information and turn it into actionable items.

A couple of posts ago I wrote about pivot tables. Pivot tables as I've already talked about are truly magical instruments to help you analyze data. But as we've just talked about, but you can easy out do a super computer, and this is based around utilizing your visual center.

The truth is the vast majority of our brain is set up to be able to process and use of visual information. If you took all the data input that you can get through your skin, your hearing, your olfactory glands and every other part of your system, you would find out that the data flowing into your brain in any one instance is roughly somewhere between 500 Mb per second to 1 Gb.

Almost 50% of that input is based on visual input.

So let's talk about this just a bit more, if 50% of the input coming into your brain is visual information, that means that your brain needs to process visual information somewhere between 250 Mb per second to 500 Mb per sec. This is no menial task. If you want to catch a ball, you brain sees it, processes it, and then it can do things like fine motor skill so that you can catch the ball.

Processing data at this rate is really amazing. As a matter of fact, for many many years inside of the high tech industry we survive on 10 Mb ethernet or something 50 times slower than the visual bandwidth going into your brain. Even today, you may know that 100 Mb per second takes a relatively sophisticated router systems. It is only with the advent of GigE that we are starting to get data rates that allow us to stream internet information at the same rate that your great, great, great grandfather could process visual information about a farm animal.

So we want to use this supercomputer to solve problems, find anomalous data, or figure out overreaching trends. This is as easy as graphing out information. You may not be able to take the integral of a sloping line, but you can say "hey that curve looks wacky!"

The thing that you need to remember, a good chart will allow you to find something in 30 seconds that would take you an hour's worth of analyzing a number table. If you want to find something out, make sure to chart out.

This is the fastest easiest way of tapping into your hidden talent. You need to tap into your visual supercomputer.

The question then becomes is "Why don't we use more charts in our data analysis?"

The answer is because they are really hard to make. Even if you use a tool like a spreadsheet which is exceptionally useful in making charts easier to get to, it still takes a considerable amount of effort to quickly get out a series of charts using data.

Therefore we turn our attention to one of the most underutilized parts of spreadsheets today, this is called the pivot chart. A pivot chart allows to make different charts almost instantaneously once you have set it up correctly.

The big problem is the initial set up. Unfortunately, if you think that Pivot Tables are a bit hard "to get" then you will find out that Pivot Charts are even more hard "to get."

But when you get a hang of using them, they will literally become the best instrument at your disposal to be able to make convincing arguments and find trends. They will help you be successful. The good thing is that once you "get" a Pivot table or a Pivot chart, they will become second nature. Once you learn to ride a bike, you never forget. Once you get a pivot chart and table, it'll stay will you for life.

So let us go back to the post that we did on pivot tables. If you remember we were comparing three salespeople the way that they were selling hard disk drives over a series of months and a series of different hard disk drives. If somebody handed you this pivot table and you had spent a little time figuring out how to use a pivot table, it would become one of the most useful items in your arsenal. However now we are going to take the next step.

To create a pivot chart the first thing you need to have is a pivot table. I am going to be a little unfair because I have not told you yet how to create a pivot table. We'll get to that in a later post. What I want to show you now is the power of the pivot table.

So how do you create a pivot chart?

If you look at the second picture in this post above, you will see that I have placed my mouse cursor over the current pivot chart. You can then right click on the pivot chart, and an option to create a pivot chart will come up on the context menu. Alternatively, you can go to the Insert -> Chart menu. Excel knows that you can only create a pivot chart from a pivot table. Bingo. You have just created a Pivot chart.

(By the way, these charts were made with Excel 2003. Now, I have Excel 2007. Unfortunately, Excel 2007 is probably the worst upgrade made by man in the pivot table section. They took what was a beautiful analytical tool and removed a couple very important buttons off of the interface. If you are stuck with Excel 2007, don't worry you can still do everything they used to be able to do in the old version, it just does a little bit more complicated. However, it is things like this that makes me wonder how Microsoft stays in business.)

Now what do we get once we hit that magical pivot chart button? The answer? We initially get a chart that looks like an absolute mess. This is shown directly above.

This is where I noticed most people stop trying to work with a pivot chart.

See the funny thing is that hitting the button to make a pivot chart doesn't do the same things as when you make a normal chart. Most people have made a normal Excel chart. Every time that they've push the button to turn a table into a chart, Excel auto magically makes a very nice looking chart. However here for the first time, they hit the charts button and suddenly they simply get a mess.

I am now going to give you the secret of the pivot chart. By and large I've never seen this described very well, and yet it is incredibly simple. To turn this chart into something that makes sense, you simply drag what ever you are using for the x-axis down underneath the x-axis.

In our case we want to grab the little gray box that says months, and we want to drag this little gray box down to the bottom of the screen until a little blue box pops up which is the landing area for the months. We then want to take the other boxes on the chart and drag them up to the top of the chart and again that you will see a little blue box appears that will serve as the landing area for these gray boxes that you drank up to the top. In our case, we are going to drag the salesman box up to the top and we are going to drag the RPM box up to the top.

You now have a chart that looks like any normal bar chart that you could make from any normal source of information. However the secret is that you can instantaneously create new charts I simply using the pulldown menus.

Unfortunately I don't have enough time in the short blog post to take you through all of the different options. Ignoring that we don't have enough time, the good thing is now you have a chart that looks like a normal chart. The rest becomes very very simple. As I described if you want to see a chart of any individual salesperson, all you need to do is hit the little arrow on the pulldown. This will allow you to see an individual chart of any one of the three different salespeople. If you want to see a chart of the various RPM sales or you need to do is use the pull-down menu.

So suddenly you can start making all types of charts with one or two different mouse clicks. Once you start creating new charts, you will start to notice if data looks like bad data. You will also notice trends that would otherwise be unnoticeable.

We use our eyes to keep ourselves from walking into walls and hurting ourselves. A good pivot chart uses your eyes to keep you from making mistakes.

Use a chart to discover the truth.

And the best way to make a chart is through the pivot process.