Project Management Dashboard – Excel with traffic lights

 Finance, Technology, Work  7 Responses »
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 :
=trafficlight(Sheet2!B4,Sheet2!B$3,Sheet2!C$3)
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.

Notes:

  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.

Europe 1 – America 0

 Finance, Politics  No Responses »
Oct 152008
 

Quite astonishing that for once the European politicians have got their act together and addressed the financial cesspool that the banks have created. A shining performance by Sarkozy and Brown, from whom nobody expected such alacrity and cooperation.

On the other side of the pond (is Bush still president over there?), McCain is too busy slinging mud at Obama in a last-ditch effort and Paulson’s plan seems rather ineffectual by comparison to the Europeans’.

It seems that Europe is finally assuming the role it should take; with a population of some 450 million it’s about time.

Insurance price according to your passport?

 Finance, Politics  No Responses »
May 312007
 

I was flabbergasted to learn that the price of car insurance in Switzerland is a function of your nationality. The surcharge can be as high as 97% if your unlucky enough to be from pretty well anywhere in the sourth-eastern Medditerranean area and, curiously, anywhere in north or south America (article in French).

The insurers argument is that drivers from certain countries statistically have more accidents. Now I can follow the reasoning, the problem is that each company has wildly differing prices for a given nationality, which to me smells of poor statistics. If the sample is large, the surcharges should be approximately the same.

What’s even worse is that the no-claims bonus is aplied on the surcharged price. This means that a driver from country X, whose initial policy has a 94% surcharge, who has no accidents for 10 years is penalised forever.

Legal as it may be, this practice stinks of racism. Shame on the Swiss; are they alone in doing this?

 Newer Entries