Wow, this is one of the coolest features on flickr, you can use instant zoom to see finer details of pics (the ones which have all sizes allowed for anyone).

FlickR Instant ZOOM Feture

• 0 Comments

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?

• 8 Comments

Environmental Graffiti should get the award for “worst possible bar chart ever” for this unbelievable piece of art…

Image (c) of Environmental Graffiti

Who said bar charts are only for serious data interpretation, they can be used to have such fun

Also read Garr Reynold’s comments on this as well. Happy Thursday..

• 2 Comments

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

• 0 Comments

I can never get tired of in-cell charts, whenever I get sometime, I try to experiment something on them. Here is an idea to design true incell column charts without using any add-ins or installing fonts. These charts can be fun to have on your project report or annual news letter or memos, they take as much time as munching a handful of M&Ms.

  Download excel chart containing incell bar chart example

1. First we will create in-cell chart using rept() the usual way

This is the simple step. For a sample data, we can use rept() excel function to repetitively print a symbol. Thus, by repetitively printing “|” (pipe) we can create incell bar charts.

REPT() example is shown below:

=rept("|",10) will print ||||||||||

2. Next combine several incell charts to create a column chart

We can combine various incell charts using excel’s concatenate() function or & operator. Better still you can use the concat() UDF. The trick here is we will delimit each chart with an ENTER key code by using char(10) as the delimiter. When used along with “wrap text” cell format this key code will force cell contents to a newline where the char code 10 is inserted.

3. Finally rotate the cell contents by 90⁰ to make the charts vertical

Just select the cell contents, hit ALT+1, and set the alignment formats so that the text is vertically aligned in the cell, and turn on wrap text.

That is all, you will now have an incell bar chart that is vertical like the one below:

Pretty cool eh?

• 8 Comments

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.

• 2 Comments

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

• 3 Comments