Oct 162008

Many traffic-light solutions for Excel exist but all the ones I tried only work for a single light, and I needed an array like this:

I wanted to create a light by simply typing a formula in the light’s cell, with the colour of the light determined by a value in cell elsewhere. The light must change colour as soon as the underlying value changes. This wasn’t as simple to implement as I thought, but in the end a bit of VBA led to this formula :
and the rest of the lights are created by dragging this formula right and down.

The parameters to the TrafficLight function are:

  1. The cell containing the value which determines the colour of the light.
  2. The threshold to change from red to amber.
  3. The threshold to change from amber to green.

in this example, the Sheet2 looks like this:

Project 1 has a value of 64 and thus is amber.

Here’s a ZIP file with the .XLS and the images project-progress-dashboard, feel free to use it as you see fit.


  1. The red and green images have an exclamation mark and a tick superimposed so that they are recognisable on a black-and-white printout.
  2. You must keep the 3 GIF files in the same directory as the spreadsheet.
  3. There is one bug. If you resize a cell containing a light such that the light is no longer contained within the cell’s boundaries, the light will not be deleted when its underlying value changes (you end up with an orphaned light). To fix this, there is a “Remove lights” button; clicking it will delete all images and pressing F9 will re-generate correctly.
May 252007

A good portion of our lives is spent trying to make things work, be they a microwave oven, a coffee machine or, God help me, a television.

Our first television was in 1962, with two channels, BBC and ITV. It was black and white with a resolution of 405 lines. (BBC2 introduced 625 lines in 1964). There were 4 controls: on/off/channel, volume, brightness and contrast. Here we are, 45 years later, with fundamentally the same primitive technology, and the most ghastly user interface and technology produced by man.

The TV industry has been plagued by a plethora of so-called standards: the American NTSC, the British PAL, the French SECAM, and a host of variants, all mutually incompatible. The result is that viewers who want to watch programs from neighbouring countries have to buy much more expensive dual-standard televisions. Concomittantly, enormous sums have been spent trans-coding programs between standards, with consumers footing the bill.

The video industry fares no better. No sooner were video casettes invented, manufacturers developed mutually-incompatible formats (VHS, BETAMAX, VIDEO 2000), again forcing consumers to buy dual-standard players. Learning nothing from past mistakes, the DVD industry also started a standards war (which was finally settled by Lou Gerstner). But true to form, they quickly created two dual-layer recoding standards, DV- and DV+, so that the public could be confused and once again milked. An identical standards war is now in progress for the next generation, between  Sony/Panasonic’s Blu-ray Disc, Toshiba’s HD DVD and Maxell’s Holographic Versatile Disc.

Let’s take a look at some parallel technologies. Not one has made such a pigs ear of it as television:

Compact Disks (CDs) were coined barley 20 years ago. The technology was well thought-out, cheap and perfectly standardised; the proof of good design is that it hasn’t had to change since its inception.

Kitchen appliances have evolved immensely. Microwave ovens, also born in the late 70’s, have revolutionised cooking and their manufacturers have delivered carefully crafted interfaces, comprehensible even by those whose IQ doesn’t exceed their shoe-size:

The telephone (fixed, cordless and mobile GSM) is a further technology whose engineers have produced miracles. My mobile phone not only allows me to talk to anyone on the planet instantly, but also has a camera (still and video), remindes me of my appointments and displays my emails. The palm-sized package communicates over GSM, USB and Wifi (802.11b/g), has a display quality that rivals current television and costs less than half the price of a television receiver.

Television technology is pathetic and it’s getting worse. Here is the ghastly botch-up that I am compelled to use if I want to watch TV:

125 buttons just to watch a TV program? 
It patently hasn’t occurred to the half-wits who designed this rubbish that my real needs are:

  • Choose “BBC1” or “SF2” or some other acronym that I can remember easily
  • Play a DVD
  • Record a program for later viewing

None of the 125 buttons point me intuitively to meet those needs. What the hell are all those random colours and unrecognisable icons for? Why must I have three different controls? (Yes, I know that some remotes can double for their buddies, but I’ve yet to meet someone who can manage the feat). I can’t remember that BBC1 is channel 491, why the hell can’t I have an alphanumeric keyboard? A PC keyboard has less buttons.

The worst interface created by man.

P.S. We have a television, but I never watch it.

 Posted by at 10:11 pm  Tagged with: