Pointy Haired Dilbert - Chandoo.org

Google Maps Directions by Public Transport

Wow, this is awesome. Google maps has added directions by public transport. Sometime back when they integrated public transport timings along with bus-stop / train station icons, it was pretty cool, but adding directions based on bus / train routes is totally awesome. Try it for yourself, click here to see a sample direction.

We have been debating whether or not to buy iPhone. For one, we are not on any cellular contract and free to go. But the sheer cost of $400 one time (one for my wife and one of me) and a monthly bill of ~ $150 is holding us back. Our current mobile bill is roughly $80 per month and when we choose to go back home we can just takeout the SIM Cards and thrash it without having to bother about paying any contract breakage (incidentally for iPhone this could be as much as $175 per line). On the other hand, the iPhone is excellent value for money for the awesome features it provides. Our current phones were bought at Rs. 10,000 a piece (roughly $250), even though they were world class when we bought them 2 years back and still work well, their features are no where comparable to that of iPhone’s. Hmm…

Here are few excellent excel links from the last week around the web:

• Learn about excel category axis, find out how to use dates, numbers, text etc. on category axis for charts. See how you can tweak the axis to get the outcome you want (for ex: How to show weekdays on a date axis, how to configure axis for stock ticker charts etc.)
• If you are the kind who would install lots of add-ins to do your work, then you may be wondering how to find out what are the add-ins you have in place. Find out all the COM add-ins you have installed in Excel from Daily dose of Excel
• Winners of 2008 Excel Dashboard competition, see the beautiful and informative dashboards here 1, 2 and 3. The winner got an iPhone, alas, if only I knew about this earlier, I would have wondered “how to dashboard” instead of “to iPhone or not to iPhone”.

Got a hot excel tip to share, drop me an email or leave a comment.

Bullet graphs provide an effective way to dashboard target vs. actual performance data, the bread and butter of corporate analytics.

Howmuchever effective they are, the sad truth is there is no one easy way to do them in excel. I have prepared a short tutorial that can make you a dashboard ninja without writing extensive formulas or installing unknown add-ins. So get out your shinobigatana and join me in a fresh excel sheet arena.

Before we create our first bullet graph, let us spend a few moments understanding these graphs. Stephen Few proposed bullet graphs as way to provide crisp view of “target vs. actual performance” numbers. Shown below is a sample bullet graph and how you would read it.

Read up more on this at PTS blog and on a Gauge chart that actually works.

Let us create your first bullet graph

Click here to download bullet-graph template excel sheet so that you can see while reading

Our technique of involves conditional formatting and simple formulas applied to a cell grid. Just follow these 4 easy steps:

Step 1: Prepare your data for charting

Since we are going to plot bullet graphs on a cell grid, we first need to normalize our data. I have chosen to plot each bullet graph on 20 cells in a row as shown in the raw grid shown to the right:

Assuming we have fictitious sales data like this:

You can normalize YTD sales figures using a simple formula like this : ROUND(YTD-sales/target*20,0)

Now that we have our data steaming hot, lets brew the graphs

Step 2: Lets make the raw grid formatted based on data

Now we will take the raw 20 cell grid in each row and conditionally format these cells so that we have background of the bullet graph drawn on them.

For eg. If the normalized sales data for Bad range is 7 and for OK Range is 15 then,

We will highlight first 7 cells lighter shade of gray, next 8 cells gray and last 5 cells with darker shade of gray.

I have shown the conditional formatting applied to these cells below:

When we are done, a sample row looks like this:

We have our cell grids ready now, lets shoot some bullets.

Step 3: Plot bullets on our graph canvas

Our final step involves print a bullet symbol (either – or + or | ) in each cell depending on one of the following conditions:

1. If the cell position (1,2,3 … 20) is equal to Year ago value and cell position is less than YTD value print a + symbol
2. If the cell position is equal to Year ago value and cell position is more than YTD value print a | symbol
3. If the cell position is less than YTD value print a –
4. Else print a blank

See the formula below:

Download the excel template for bullet graphs to understand this formula better

Step 4: Show off your bullet graphs, awe your boss or colleagues, bask in your Ninja glory

Unfortunately, I cannot tell you how to do this. I can only teach you to be a Ninja, but you have to be one to charm people with your tactics.

Shown below is another variation you can try. Also, you can experiment with the symbols printed (instead of + - | you can try other ASCII characters, for more download the excel sheet containing bullet graph templates)

Also try: Partition charts, Incell Graphs and much more.

In response to Making Incell charts better article, reader Tony mailed me an excel sheet in which he has modified the charts to display colors and negative values. This is pretty cool. Take a look at the chart below:

MS Excel Incell charts with colors and negative values

The trick lies in displaying the charts in 2 rows, one for positive values and another for negative values and then coloring positive row with green and negative row with red. See aside.

Download this example sheet to experiment with incell charts.

Thanks Tony for sharing this with our readers

What is your favorite charting trick?

I am fascinated by board games. They provide immense fun, anyone can enjoy them, they are unpredictable and best of all they are great value for money. That is why whenever I get sometime I experiment with simulating games to know them better [read Why Monopoly board game is not as random as it appears]. So, out of curiosity I have created an excel sheet that can generate bingo / housie (housey) tickets - 24 of them at a time. To get new set of tickets you would hit F9 (recalculate).

  Download Bingo / Housie ticket maker excel sheet

Note that these are Bingo UK / India / Australia variant I am talking about, not the US 5*5 type of bingo tickets.

Read on if you want to know how this is done:

According to Wikipedia:

A typical housie/bingo ticket .., contains fifteen numbers, arranged in nine columns by three rows. Each row contains five numbers and four blank spaces. Each column contains either one, two, or very rarely three, numbers:

* The first column contains numbers from 1 to 9,
* The second column numbers from 10 to 19,
* The third 20 to 29 and so on up until the last column, which contains numbers from 80 to 90.

I have removed number “90″ from the list in order to reduce some complexity in generating the tickets.

The problem is now to “generate 15 random number between 1 to 89 and fill them in 15 random spots in a grid of 3 rows by 9 columns such that each row has exactly 5 numbers”

Now I could write a function in VBA to do this, but I wanted to do this only using formulas. So I started breaking the problem.

The first challenge is to select any random 5 cells in a 9 cell row

Once we select any random 5 cells in a 9 cell row, we will fill them with bingo numbers. Now, excel has a function to generate random numbers between 1 to 9 (=round(rand()*9,0)), but this is not good for us since each time we call this function we will get a random number between 1 to 9, where as we need a 5 random numbers without repetition between 1 to 9. The function is memoryless and could repeat numbers when called 5 times.

Instead we can list all the possible “5 cells with numbers and others are empty” combinations of a 9 cells region and select a random combination every time. There are essentially ⁹C₅ i.e. 126 ways in which you can select any 5 cells out of 9 cells (without repetition of course).

So I listed all these combinations in a table and then randomly selected one of the combinations. You can see the first five such combinations in the image below:

Selecting any five cells out of nine cells

Now I created a 3*9 region and filled the cells with 1s or 0s, “1″ when the cell in bingo ticket is supposed to have a value and “0″ if the cell is empty as shown below:

Next challenge is to show random values in each cell

The trick here is that first column in our 3*9 bingo ticket has any number(s) from 1 to 9, second column has any number(s) from 10 to 19 …

Again, the challenge is the numbers should not repeat, otherwise we could simply use rand()*10, rand()*10+10, rand()*10+20 ... to generate the numbers.

This time it gets even more trickier because each column can have either no values, or 1 value or 2 values or 3 values.

The ticket generation logic now looks like:

• If the column has no values in it, then we will leave all the cells in that column of bingo ticket empty
• If the column has 1 value, we will generate any random number from that column’s range of possible values (1-9, 10-19,20-29,…80-89) and place it in the cell that is supposed to have a value and leave other cells empty.
• If the column has 2 values, we will generate 2 random numbers without repetition from that column’s range of possible values and place them in cells that are supposed to have them
• If the column has 3 values, we will generate 3 random numbers without repetition from that column’s range of possible values and place them in cells that are supposed to have them

As you can see, it is easy when the column has no or 1 value in it. But when the column has 2 or 3 I used the combinations trick described earlier.

First I created all 2 number combinations and 3 number combinations. Since the numbers on Bingo ticket are always sorted from top to bottom in a column, I just had to list down 45 combinations (¹⁰C₂) for 2 numbers and 120 combinations (¹⁰C₃) for 3 numbers.

The rest of the details are small enough that I can leave them to your imagination. So when the ticket is generated, it looks like this:

Remember to download housie / bingo ticket generator excel sheet and print your tickets at home. Just F9 to generate new set of tickets. Un-hide the rows from 43 if you want to see how this is done.

Like this post: Consider digging it or bookmarking it on del.icio.us or better still subscribing to my feed, its yummy

Each of us have our mechanisms to track how we spend money. We use Excel, various online sites and software like Quicken or Microsoft Money to track how we spend our bucks. The bottom line is to track where each penny / paisa / cent is going. So, how great would be it be, if by a click of mouse you can open a form that can take details of what you have spent the money on and add the details to the end of a huge list and let you analyze the expenses at leisure?

Well, you can do that using Google docs - spreadsheet forms with 4 simple steps. Doubt it? Take a look at expense tracker form that I have created here.

1. First create a new Google docs - spreadsheet.

Once you are inside the spreadsheet click on the “forms” tab as shown below.

This will open a new window where you can create a form. We will use this form to enter spending details.

2. Create a basic expenditure tracker form

We will now create a basic form with the following fields:

1. How much you spent?
2. What did you spent it on? A description for the spending
3. Category of spending A list of categories from which you can choose

The first step is to name the form:

Next we will add 3 fields as shown below, the process is very easy, just try for yourself.

Finally we will save our expenditure tracking form:

3. Now lets start entering expenses in our form

When you are done, click on the “Next, choose recipients” option. This screen will show the form:

Bookmark the form url shown in the screen and this is the url we will access whenever you want to enter new expense.

Once you enter few expenses the spreadsheet will look like this:

See how Google adds the time stamp to each expense. This is a good thing as you dont have to enter the expense date. You can use this column to see how much you are spending everyday (provided you enter information as soon as possible)

4. Analyze the tracked expenses

What is the use of tracking when you are not analyzing. You can use built-in charts in Google docs to do some analysis of the expenses. I did a bar chart to show what is possible.

Take a test drive at the expense tracker form by accessing the one here.

Photo from pfala.

How geeky are you? Show off your geekness to others with this kickass room decor ideas,

Just click the images to follow up. live geek, love geek

1. Lets start with the door. You can experiment with the door locks to create your own puzzle:

2. Now that you are in how geeky would you like to make your sofa? how about a pixel couch?

3. Dont fancy a couch, why dont you create your own ball-pit?

Not sure how much this would cost, you can find out using this ball-pit-pocket-hole-calulator

4. Now that you are sitting, what about those window curtains, would like them ASCII?

5. May be ASCII is a bit too old, why dont you buy a G(O)oogle saree and hang it up as a curtain?

At 299.98, this Ooogle sari can make great (expensive ;)) curtain

6. what about the ceiling, isnt it too bland, why not decorate it up with old CDs & DVDs?

7. Now that the ceiling is taken care, what about that dull wall paper, why not mario-fy it?

8. What about the lighting in the room, why the lame lights when you can DIY some ambient lighting?

9. Now lets geekify the furniture, how about a Tetris book shelf?

10. Finally what is a room without something to eat, why not say hello world with slice of bread?

So what are you waiting for? Go ahead and make your room geekier than ever. Happy Friday

Images are not mine, hosted here. Click on them to see the original ideas and make your room geeky

Also : Make your charts super cool