S Anand

Split text

This is a series on what Google Spreadsheets can do that Excel can’t.

SPLIT(string, delimiter) splits a string using a delimiter. So if you have "one,two,three,four" in cell A1, you could split that into 4 cells using =SPLIT(A1,",")

3.1

That’s similar to Data > Text to Columns, except that if the original data changed, Text to Columns does not revise the output. SPLIT can give you dynamic text-to-columns. This is pretty useful when processing text data, in three ways:

  1. You retain the original data
  2. You don’t need to re-apply Text to Columns. Extending the formula will work (and that’s quicker)
  3. It’s dynamic. If the data changes, your split changes

Since SPLIT returns an array, you can do a bunch of useful things with it.

=COUNTA(SPLIT(A1," ")) gives you the number of words in a string

=SUM(SPLIT(A1,",")) sums up a comma separated list. "1,2,3,4" is added up to 10.

=ARRAYFORMULA(SUM(LEN(SPLIT(A1,",")))) sums up the word lengths. So "one,two,three,four" splits into 4 words of length 3,3,5,4 each, which adds up to 15.

The ability to join and split also lets you sort by multiple keys. For example, say you had income by country and product. You want to show it sorted by Country & Product. You also want to show it by Product & Country.

So first take the data sorted by Country and Product.

3.2

Now, in column E, create a key that’s sorted by Product and then by Income. Type

=SORT(ARRAYFORMULA(B2:B10&":"&A2:A10&":"&C2:C10))

… in cell E2. That will give you all the data in one cell, sorted by Product and then by Country. Now, just split the data, as shown here.

3.3

Note: You could have done the whole thing using one formula:

=ARRAYFORMULA(SPLIT(SORT(ARRAYFORMULA(B2:B10&":"&A2:A10&":"&C2:C10)),":"))

But for some reason, this doesn’t seem to show the first row properly. No idea why.

Can I do that in Excel?

Well, not really. You’re best off creating a user-defined function to duplicate the SPLIT function.

Dynamically sort data

This is a series on what Google Spreadsheets can do that Excel can’t.

To sort data, use the SORT function.

For example, if you have a list of products, their revenues and profits in A2:C9. Type SORT(A2:C9, 2, FALSE) in cell E2 to get the products sorted by the second column, revenues.

2.1

This is a dynamic list. If you change the revenues, the products are reordered automatically.

The first parameter to the SORT function is the data range you want to sort. The remaining parameters are optional. The second parameter is the column to sort by. By default, the data is sorted by the first column, in ascending order. In this example, we sorted by the 2nd column. The third parameter is FALSE for descending order, and TRUE for ascending order.

You can specify additional columns to sort by. Just add the second column number and the order, third column number and order, and so on.

For example, this formula sorts by the 2nd column (ascending), 4th column (descending) and 1st column (ascending):

=SORT(A1:D100, 2, TRUE, 4, FALSE, 1, TRUE)

You can create a dashboard with multiple views. Say you wanted to show the above data, and also summarise the top 3 products by revenue and profitability. Go to cell E2, and type

=NOEXPAND(SORT(A1:A9, B2:B9, TRUE))

This sorts the products (A1:A9) using the revenues (B2:B9) in ascending order (TRUE or 1). This would show all 8 products. If you want to keep only the top 3, you need to put the NOEXPAND around the formula. Otherwise, even if you delete the 4th product, Google will put it back.

Now, delete all but the top 3 products. Similarly, in cell E7, type

=NOEXPAND(SORT(A1:A9, C2:C9, TRUE))

This sorts by profitability instead. That’s it! You have a dynamic list of the top 3 products by revenue and profitability.

2.2

Can I do that in Excel?

Excel doesn’t have a function to sort. You can sort a list in-place. That changes the order permanently. There’s no way of retaining the original order.

You could make a copy of the list and sort it. But the copy will not change when the original list changes.

If the length of the list is fixed, and the values you want to sort by are unique, you could use the LARGE/SMALL, INDEX and MATCH functions to simulate this effect. First, type the numbers 1-8 in column D. Then type this formula in F2:

=LARGE(B$2:B$9,D2)

This will give you the largest revenue figure. Copy this down the column. This will show the largest revenue figures in descending order. Now, fill cells E2 downwards with the formula:

=INDEX(A$2:A$9,MATCH(F2,B$2:B$9,0))

The MATCH function finds the revenue in the first table, and the INDEX function looks up the corresponding product. You can use the same principle to get the profitability. However, this will not work if two products have the same revenue figure.

2.3

Keyword searches as a Web command line

Andre’s mentions dumping Google Chrome because of lack of extension support, especially Ubiquity, and lists 15 useful Ubiquity commands.

If you haven’t seen Ubiquity, you should. It’s a great extension that transforms your browser into an Internet command prompt. It is modelled on the Enso Launcher, which is a great piece of work by itself.

I wasn’t quite prepared to let go of Chrome that easily. On Task Manager, seeing 10 Chrome processes, the largest of which takes up 60MB, is a lot more comforting, psychologically, than 1 Firefox process taking up 300MB. (I rarely hit my 1GB RAM limit, so it shouldn’t matter either way. Yet, the spendthrift in me keeps watching.)

So the question is, can I do all the items on his list without using Ubiquity?

Let’s pick the easiest. Google search. If you typed "g some words" on Ubiquity, you get the Google search results for "some words". But you already have that. If you have Firefox, typing any words on the address bar automatically does a Google search for you. On Internet Explorer, it search live.com, but you can easily change that by installing the Google Toolbar.

But the great thing is that this can be customised. On Firefox, click on the down arrow icon next to the search box and select "Manage Search Engines…" to see a list of your search engines. Select the one you want to use, click on "Edit Keyword…" and select the keyword you want. For instance, I’ve typed "google".

Manage Search Engines Add Keywords

So when I type "google some words" on the address bar (not the search bar, the address bar) I get search results for "some words". These are called keyword searches.

On Firefox, you add your own search engines, but you do that using bookmarks. Press Ctrl-Shift-B (Organize Bookmarks) and create a New Bookmark. You can type in any URL in the location field. If you type "%s" as part of the URL, that will be replaced by the search string. So for instance, using a location http://en.wikipedia.org/wiki/Special:Search?search=%s and the keyword "wiki" will do a Wikipedia search for "Harry Potter" if you type "wiki Harry Potter" on the address bar.

It works on Internet Explorer as well, even with version 6. The easiest way is to download TweakUI. Go to Internet Explorer – Search. Click on the Create button. Type in a keyword (called Prefix) and a URL. If you type "%s" as part of the URL, that will be replaced by the search string.

TweakUI

On Google Chrome, get to the Options (what, no shortcut key?) and in the Basics tab, click the Manage button. Here, you can click on "Add" to add a search engine.

Chrome

So that takes care of all the basic searches: Google, Amazon, IMDB, Wikipedia, etc.

Can we go further? Item 8 on the list caught my attention:

Twit. As much as I love full-featured Twitter clients like TweetDeck, nothing beats the simplicity of hitting Ctrl-Space and typing twit [message] to so_and_so, or sending a selection of text using twit this to so_and_so. At the moment, there’s no way to receive tweets or ping Twitter for new messages.

I don’t use Twitter, but I do use Identi.ca, and I would like something like this. Right now, I’m using Google Talk to update identi.ca. Two problems. I don’t like chatting, and logging on exposes me to a lot of distraction. Secondly, I’d rather not have to open an application just for this. Something in the browser would be perfect. But is it possible?

Identi.ca (and Twitter, and most micro-blogging services) let you update via e-mail. So if I could write a program that would mail identi.ca, I should be done. So I did that with a Perl script.

my $q = new CGI;
open OUT, "|/usr/sbin/sendmail -t";
print OUT join "\n",
    "From: my.authenticated.id@gmail.com",
    "To: 1234567890abcdef@identica.com",
    "Subject: \n\n",
    $q->param('q');

So if I placed this at www.s-anand.net/identica (no, I haven’t placed it there), I just need to create a keyword search with a prefix Tidentica" that points to www.s-anand.net/identica?q=%s. Then I can type "identica Here is a message that I want to post" on the address bar, and it gets posted.

Actually, if you can write your own programs, the possibilities are endless. If you’re looking for someone to host this sort of thing for free, Google’s AppEngine may be a reasonable point to start.

But the real power of this comes with Javascript. Those URLs that you saw for keyword searches? Those can be Javascript URLs. So item 9 on the list

Word count. As a student of copywriting, I’m frequently curious about an article’s word length. Highlighting the desired text and entering word count into Ubiquity will give you just that.

… might just be possible.

It’s easy to get the selection. The following snippet gives you the current selection. (Tested in IE 5.5 – 8, Firefox 3 and Google Chrome. Should work for Opera, Safari.)

document.selection ? document.selection.createRange().text :
window.getSelection ? window.getSelection().toString() : ""

To get the word count, just split by white space, and count the results:

s = document.selection ? document.selection.createRange().text :
    window.getSelection ? window.getSelection().toString() : "";
alert(s.split(/\s+/).length + " words")

Now, this whole thing can be made into a keyword search. Let’s call it count. If I go to the address bar and type "count it", I want to use count the words in the selection. If I typed "count some set of words here", I want to count the words in "some set of words here". Here’s how to do that.

javascript:var s = "%s";
if (s == "it") {
  s = document.selection ? document.selection.createRange().text :
      window.getSelection ? window.getSelection().toString() : "";
}
alert(s.split(/\s+/).length + " words");

Now, put all of this in one line and add it as your keyword search. Try it!

(Note: You need to replace { curly braces } with %7B and %7D in Google Chrome. It interprets curly braces as a special command. Also, Chrome replaces spaces with a +, so the word count will always return 1 if you search for "count some set of words here".)

You could use selections to search as well. If you wanted to Google your selection, just use:

javascript:var s = "%s";
if (s == "it") {
  s = document.selection ? document.selection.createRange().text :
      window.getSelection ? window.getSelection().toString() : "";
}
location.replace("http://www.google.com/search?q=" + s)

Typing "google it" will search for your selected words on Google. "google some words" will search for "some words" on Google.

I’ve configured these keyword searches on my browser to:

  • Share sites. Typing "share google" adds the page to Google Reader, "share delicious" posts it to del.icio.us, "share digg" diggs the page, etc.
  • Send mail from the address bar. Typing "mail someone@gmail.com sub:This is the subject. Rest of the message" in the address bar will send the mail out. (Of course, you need to have created a mail gateway. I’ll try and share this shortly.)
  • Add entries to my calendar. Typing "remind Prepare dinner at 8pm" adds a reminder to my calendar to prepare dinner at 8pm.
  • Highlight parts of a page. Typing "highlight it" highlights what I’ve selected on the page. Even after I remove the selection, the highlighting stays. Typing "highlight some phrase" highlights all occurrences of "some phrase" in the entire document. The colours change every time you use it on a page, so you can search for multiple words and see where how they’re distributed.
  • Replaces tables with charts. Typing "chart it" with a table selected replaces the table with a chart. Typing "chart it as pie" or "chart it as scatter" changes the chart type.

You could actually take any bookmarklet and convert it into a keyword search. Which means that practically anything you can do on Javascript can be convert into a command-line-like syntax on the address bar.

So there it is! You can pretty much have a web command line. I wonder if we could add UNIX-pipes-like functionality.

Dynamically eliminate duplicates

This is a series on what Google Spreadsheets can do that Excel can’t

To get a list of unique values from a list, use the UNIQUE function on Google Spreadsheets.

For example, if you have a list of browsers in column A, type =UNIQUE(A1:A17) at cell B1 to get a unique list of browsers. This is a dynamic list. If you change the list of browsers, the unique list gets updated automatically.

1.1

You can use UNIQUE to create a dynamic pivot table. Quite often, you end up creating a pivot table simply to summarise by one column. The main purpose the pivot table serves is in getting a list of unique values on that column. Plus, it’s a bit heavy on the UI. And every time the data changes, you need to refresh the pivot. But with the UNIQUE function, you can get a dynamic list of unique values, and you can use the COUNTIF and SUMIF function next to each value. Here is an example showing the frequency table of the browsers shown earlier. Column C does a COUNTIF of the unique values on the original list.

1.2

You can also use UNIQUE as the input to another formula:

=COUNT(UNIQUE(LIST)) counts the number of unique values

=COUNT(LIST)-COUNT(UNIQUE(LIST)) gives the number of duplicates

=INDEX(UNIQUE(LIST),3) gives you the third unique value

=LARGE(UNIQUE(LIST),3) gives you the third largest unique value

… and so on.

Can I do that in Excel?

You can, but not easily. There are two approaches, but each has its limitations.

A. Use Advanced Filters: easy but static

  1. Create an advanced filter on column A (Alt-D-F-A)
  2. Select Copy to another location
  3. Click in the Copy to box, and then click the cell B1
  4. Select Unique records only
  5. Click OK

1.3

But the list of unique values that you get here is static. If you changed one of the values, the list of unique values does not change.

B. Use a complex formulae that are dynamic

First, blank out the duplicates by typing this formula:

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

adjacent to the first cell (into B1), and dragging it all the way down (to B17).

Now, create a named range (Alt-I-N-D) for these cells (B1:B17) called WithBlanks and another named range called NoBlanks for the cells one column to the right (C1:C17).

On the first cell of NoBlanks (C1), type this formula:

=IF(ROW()-ROW(NoBlanks)+1>ROWS(WithBlanks)-COUNTBLANK(WithBlanks),"",
    INDIRECT(ADDRESS(SMALL((IF(WithBlanks<>"",ROW(WithBlanks),ROW()+
    ROWS(WithBlanks))),ROW()-ROW(NoBlanks)+1),COLUMN(WithBlanks),4)))

Press Ctrl-Shift-Enter rather than Enter, because it’s an array formula. Now drag this all the way down (to C17).

The list in column C is dynamic. If you change a cell in column A, column C is updated. But the formula can only handle one column. Google Spreadsheets’ UNIQUE function works with any number of columns. If you had data in the range A1:D100 and wanted the unique rows, UNIQUE(A1:D100) gets that for you.

1.4

Note: I’m staying away from user defined functions. You could, of course, create a UNIQUE function in Excel using Visual Basic. In fact, you should!

Mobile browsing

When I analysed my HTTP log last week, I had another motive: are there enough people accessing my site on a mobile device? Or is it too small at this stage for me to care about?

Well, have a look at the numbers.

Windows 98.4%
Mobile 0.6%
Linux 0.5%
OS X 0.5%

Yes, there are more people accessing my site through a mobile device than there are using Linux or OS X. That’s shocking!

Now, I’m not saying that this is representative of the rest of the world or anything, but at least it tells me a couple of things.

Firstly, the whole mobile browsing thing is bigger than I thought it was. I started worrying about this a couple of months ago and got myself a HTC s620 phone and a BlackBerry (for free, through some innovative social engineering and smooth talking). It really does get pretty useful on the move… which is frankly anywhere outside of the home and the office, and sometimes even within. (It’s handier to read recipes off the HTC than a laptop.) Google had caught on to the whole mobile browsing trend a very long time ago, and are rather well positioned to make use of it.

Secondly, it means that rather than worrying about my site working on Linux or OS X (i.e. worrying about what plugins to use), I should worry more about it working on mobile devices (i.e. small screen, no Javascript / CSS).

That’s a fairly big shift in my thinking. Earlier, I had been all for shifting all the processing to client-side Javascript. Now it appears I need to design more towards plain HTML pages generated by Perl / PHP.

Google Chrome screenshots

I went to the Google Chrome site.

Clicking on the “Accept and Install” button…

… automatically launched the downloader in Firefox…

… and (after a fairly short while) started installing the application directly. This may be the most painless install I’ve done in a while.

I clicked on “Customise the settings”

This is what it looks like.

And that’s it! It installs, and launches in just a few seconds. First impressions: the startup and rendering are really fast.

The address bar doubles up as a search bar. Very sensible.

Several nice features: incognito mode, application shortcuts, and developer tools.

The Javascript console has Javascript autocompletion! Watch out, Firebug.

The “Use DNS pre-fetching” looks interesting. My browsing certainly seems faster. Might be faster than Opera, even.

The “Show suggestions for navigation errors” feature.

There’s a task manager…

… that shows how much memory each site uses.

But not all is good. This jQuery animation on my site leaves trails behind.

And the text box resizing is good, but feels a bit… wrong, somehow.

Plus: I can re-import history, bookmarks, etc. from Firefox at any point, so I don’t have to worry about using this as a secondary browser.

Update (8am UK, 3rd Sep): Chrome.exe isn’t installed in your “Program Files” folder. It’s in your “Documents and Settings” folder, under “Local Settings\Application Data\Google\Chrome\Application”. (That’s on Windows XP. Not sure about Vista.)

There’s a Themes folder, so I imagine more themes should be on their way.

There doesn’t seem to be an about:config option. But there are a whole lot of others:

  • about:cache
  • about:dns
  • about:histograms
  • about:memory
  • about:plugins
  • about:stats
  • about:version
  • about:crash
  • about:internets
  • about:network
  • about:blank
  • about:shorthang
  • about:hang
  • about:objects

I’m not entirely sure if the last two work. Based on comments at John Resig’s blog. Go through the code to see if you can find more.

Attack of the bots

One out of every 5 hits to my site is from a bot.

I spent a fair bit of time this weekend analysing my log file for last month (which runs to gigabytes, and I ended up learning a few things about file system optimisation, but more on that later). 80% of the hits were from regular browsers. 20% were from robots. Here’s a sample of the user-agents:

Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)
Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
Mediapartners-Google
DotBot/1.0.1 (http://www.dotnetdotcom.org/#info, crawler@dotnetdotcom.org)
Mozilla/5.0 (Twiceler-0.9 http://www.cuill.com/twiceler/robot.html)
msnbot/1.1 (+http://search.msn.com/msnbot.htm)
FeedBurner/1.0 (http://www.FeedBurner.com)
Mozilla/5.0 (compatible; attributor/1.13.2 +http://www.attributor.com)
WebAlta Crawler/2.0 (http://www.webalta.net/ru/about_webmaster.html) (Windows; U; Windows NT 5.1; ru-RU)
Yandex/1.01.001 (compatible; Win16; I)
...

You get the idea. The bulk of these are search engines. Over two-thirds of the bot requests were from Yahoo Slurp. Now, this struck me as weird. If I take the top 3 search engines that are sending traffic my way,

  Referral % Crawl %
Google 90% 24%
Yahoo 6% 66%
Microsoft 3% 0.3%
Others 1% 9%

The search engine that sends me the most traffic is being reasonably conservative, while Yahoo is just eating up the bandwidth on my site. Actually, this shouldn’t bother me too much. It’s not taking up too much bandwidth, or even CPU usage, given that all the bots put together make up only 20% of my traffic. But somehow… it’s sub-optimal. Inelegant, even.

So I decided to take a closer look. Just how often are they crawling my site?

Yahoo Every 5 seconds
Google Every 13 seconds
DotBot Every 9 minutes
Cuill Every 9 minutes
Microsoft Every 18 minutes
Feedburner Every 18 minutes
Attributor Every 23 minutes
Yandex Every 27 minutes

Look at those numbers. Yahoo is hitting my site once every 5 seconds. No wonder there’s a help page at Yahoo titled How can I reduce the number of requests you make on my web site? I followed their advice and set the crawl-delay to 60, so at least it slows down to once a minute.

Just that one little line change should (hopefully) reduce the load on my site by around 15%.

As for the other engines, I don’t mind that much in terms of load.

  • Google, for all that it crawls every 13 seconds, has faithfully reported that it has only 11% of my site under its index, so I’ve no idea what they’re doing, but I’m not complaining about the traffic that’s coming my way.
  • DotBot. Today was the first I’d heard of them. Visited the site, and smiled. These guys can do all the crawling of my site that they like, and I hope something interesting comes out of their work.
  • Cuill, sends me 0.2% of my traffic, but it’s a new search engine, I’m happy to give it time.
  • Microsoft‘s OK, sends me a tiny stream of traffic.
  • Feedburner is just pinging my RSS feed every 18 minutes.
  • Attributor and Yandex I’m hearing of for the first time, again. Not too much load on a system, so that’s OK.

What’s amazing is the sheer number of bots out there. Last month, I counted over 600 distinct user-agent strings just representing bots. So it’s true. The Web is no longer just for humans. We do need a Semantic Web.

Animated charts in Excel

Watch Hans Rosling‘s TED Talks on debunking third world myths and new insights on poverty and ask yourself: could I do this with my own data?

Yes. Google has a gadget called MotionChart that lets you do this.

Now, you could put this up on your web page, but that’s not quite useful when presenting to a client. (It is shocking, but there are many practical problems getting an Internet connection at a client site. The room doesn’t have a connection. The cable isn’t long enough. You can’t access the LAN. Their proxy requires authentication. The connection is too slow. Whatever.)

So you need this in Excel. Let me explain a variant of the technique I described earlier.

Let’s start by creating a simple bubble chart.

For each item in a bubble chart, you need 3 pieces of data: the X-axis, Y-axis and size. This graph shows three items A, B and C in one year: 2001. To animate this, you need data for more years, so let’s create that.

The first 3 rows contain the same data as before, except that I’ve added a "Year" column and a "Key" column (which is just a concatenation of the Year and the Item). The data now goes on for many more years.

Now we need to create a scroll bar that can be used to change the year. So add a scroll bar below the bubble chart…

… and right click the scroll bar and go to Format Control. Now, select the cell link to some cell ($H$1 in this case). Now, if you move the scroll bar, the cell value will change.

All you need to do is to now change the source data for the chart based on the year. From the table on the left, VLOOKUP the year + item, and put this into the table on the right. When the year in the cell H1 changes, the data updates itself. So now, as you move the scroll bar, cell H1 changes, then so does the data and hence the graph.

This is what the animation looks like.

And here’s the Excel file.

Resolving the Prisoners Dilemma

If you’re ever taken a course in Economics, and it discussed Game Theory, you may be familiar with The Prisoner’s Dilemma. Roughly, this is the problem.

Assume you possess copious quantities of some item (money, for example), and wish to obtain some amount of another item (perhaps stamps, groceries, diamonds). You arrange a mutually agreeable trade with the only dealer of that item known to you. You are both satisfied with the amounts you will be giving and getting. For some reason, though, your trade must take place in secret. Each of you agrees to leave a bag at a designated place in the forest, and to pick up the other’s bag at the other’s designated place. Suppose it is clear to both of you that the two of you will never meet or have further dealings with each other again.

Clearly, there is something for each of you to fear: namely, that the other one will leave an empty bag. Obviously, if you both leave full bags, you will both be satisfied; but equally obviously, getting something for nothing is even more satisfying. So you are tempted to leave an empty bag. In fact, you can even reason it through quite rigorously this way: "If the dealer brings a full bag, I’ll be better off having left an empty bag, because I’ll have gotten all that I wanted and given away nothing. If the dealer brings an empty bag, I’ll be better off having left an empty bag, because I’ll not have been cheated I’ll have gained nothing but lost nothing either. Thus it seems that no matte what the dealer chooses to do, I’m better off leaving an empty bag. So I’ll lease an empty bag."

The dealer, meanwhile, being in more or less the same boat (though at the other end of it), thinks analogous thoughts and comes to the parallel conclusion that it is best to leave an empty bag. And so both of you, with your impeccable (or impeccable-seeming logic), leave empty bags, and go away empty-handed. How sad, for if you had both just cooperated, you could have each gained something you wanted to have. Does logic prevent  cooperation? This is the issue of the Prisoner’s Dilemma.

There’s nothing wrong in the logic, actually. The key assumption is that it is clear to both of you that the two of you will never meet or have further dealings with each other again. If you’re never going to deal with someone again (and hence there is no question of retribution or any fallout), you really should cheat.

An aside. During my first few days at IIT, two third years were ragging me about my stance on pre-marital affairs. After trying my best at defending the moral standpoint, I finally confessed that it was only the fear of the after-effects that worried me.

"There’s this beautiful naked girl in your room," they said. "You are guaranteed no repercussions. What will you do?"

"No repercussions?"

"None whatever."

I thought for a while. "I’ll flip a coin." 🙂

Anyway, the aside aside, the solution to the one-off Prisoner’s Dilemma is for both people not to cooperate. If contracts are not enforceable, we’re all better off not trading. If there are no cops, we’re individually better off stealing.

But, of course, that’s not true in the real world. Most situations are repeatable, and you do tend to meet people again. Those you cheat may even have a motivation to meet you again.

This is the iterated Prisoner’s Dilemma. Douglas Hofstadter wrote about this in the May 1983 issue of Scientific American in his Metamagical Themas column (which, by the way, is brilliant). While the Prisoner’s Dilemma has a simple solution (don’t cooperate), the iterated Prisoner’s Dilemma does not have a predetermined solution. At best, you can have a strategy. (That can be proven. If there was a predetermined solution, your opponent would know it, and could beat you. One of those cases in mathematics where you are not guaranteed a solution.)

But is it possible for cooperation to emerge in an iterated Prisoner’s Dilemma? Can it beat competitiveness?

Robert Axelrod of U.Mich conducted a computer tournament to find out. He invited strategies from game theorists and wrote them as BASIC programs. Each program would be pitted against another. Every time, it could response with either C (cooperate) or D (defect). Cooperation gets both programs 3 points. If one defects, the defector gets 5 points and the cooperator gets nothing. Both defecting gets 1 point each. Axelrod ran each program against each other many times, and added up the scores.

The program that won was called TIT FOR TAT. It was the shortest program (4 lines of BASIC code). Here’s it’s strategy:

Cooperate the first time.

Do what your opponent does thereafter.

Think about it. TIT FOR TAT starts by being nice, and stays that way, unless you defect. Then TIT FOR TAT punishes. If you repent, TIT FOR TAT forgets and forgives. Interestingly, TIT FOR TAT can never win a game. It can, at best, draw a game, but never score more points than its opponent. It goes for winning the war by losing battles.

That’s four traits:

  1. Being nice
  2. Punishing immediately
  3. Forgiving immediately
  4. Willing to lose battles

After publishing these results, and having learnt a lot about different strategies, Axelrod repeated the tournament. Four times as many entries poured in. This time from world experts on game theory. It also included an improved TIT FOR TAT called TIT FOR TWO TATS, which is an improved TIT FOR TAT that does not fall into a C – D – C – D cycle when playing against TIT FOR TAT.

TIT FOR TAT won again.

Till date, TIT FOR TAT remains an unbeaten individual strategy, and people believe it may be optimal.

(PS: By individual strategy, I mean that there are multiple programs that can team together, losing to each other and making sure that one of them wins. This sort of thing can beat TIT FOR TAT. But no individual program beats TIT FOR TAT.)


In our first term at IIMB, we played a game in our organisational behaviour class, intended to help us understand inter-departmental cooperation (or rivalry). The class was split into two ‘companies’. Each company had four divisions.

The game had 10 rounds. In each round, every division could choose to cooperate or defect. If everyone cooperated, each division made 3 points. If any division defected, it would make 5 points, while all cooperating divisions made 0 points. If all divisions defected, they would all make 1 point. The divisions were not allowed to talk to each other.

The aim was to beat the other company. (Not other divisions, within or outside the company.)

Our company started off with 3 Cs and a D, which quickly deteriorated to 1 C with 3 Ds by round 6. At round 7, it was 4 Ds.

Before round 8, we were all given a chance to have a huddle. A representative from each division would come together and talk things through. We promised to cooperate, and thereafter, it was 4 Cs to the end.

We lost the game. The other ‘company’ had started off with 1C and 3Ds, but had learned to cooperate pretty quickly, aided, in Prof N M Agrawal‘s words, by "… Aparajita threatening the other divisions with her glares."


The reason there’s a Prisoner’s Dilemma is the inability to reliably communicate or enforce behaviour. Having a chat helps. Having laws that punish you helps. Having a bully threaten you helps. The thing is, you need a signal of some kind. And it needs to be an early signal, or you end up waiting till round 8 and lose the game.

If you’re ever in a situation where cooperation helps everyone, but it’s not in interest to cooperate, here’s what seems to work:

  • See if you can agree to cooperate before-hand
    1. Have a chat
    2. Find policies that punishes defectors
    3. Threaten if required
  • If not, then try to force cooperation by signaling.
    1. Be nice
    2. Punish defection immediately
    3. Forgive repentance immediately
    4. Lose battles to win the war