Monthly Archives: April 2010

How to make nicer graphs in Microsoft Excel

I really hate it when I’m presented with a graph like this one:

Ugly default graph

It’s instantly recognisable as one created in Microsoft Excel. For some reason the default settings in Excel produce some of the the ugliest graphs I’ve ever seen.

Step 1 – Fix the data points themselves

Nobody in their right mind plots data points as little diamonds – we use crosses. Selecting “Format Data Series” enables you to set the background colour to Transparent and the style to a little cross. The colour of the cross is up to you; I tend to stick with traditional black.

Step 2 – Get rid of the background

Grey background? Why, Excel, why? Set the ‘Plot Area’ colour to none.

Step  3 – Gridlines

“Standard” graph paper tends to have two sets of lines – thicker major gridlines and thinner minor gridlines. I see no reason why Excel graphs should be any different. Under “Chart Options” there are checkboxes for major/minor gridlines on both the x- and y-axes.

I find that setting the minor gridlines on both axes to a pale grey makes things look nicer. If you’re going to print your graph (or photocopy it) then you’ll want to increase the thickness of the lines too.

Step 4 – The legend

Why Excel puts the legend on the right of the graph I don’t know. Most of the time it’s not needed, and when it is needed it’s far more space-efficient to put it at the top or bottom of the plot area.

Step 5 – Line of best fit

Excel calls lines of best fit ‘Trendlines’ so make sure you add a trendline. Make sure that you choose the right type – exponential for radioactive decay, power for simple pendula, etc.

Step 6 – Axes

Your axes need to be properly labelled.

It’s a good idea to adjust the scales as well.

The Finished Product

Probability and the Grand National

Entropy is a very important concept in physics. When I was taught about entropy it was always related to probabilities, and the problem sheets we did often featured betting, horse races, greyhounds, etc. All this came back to me this weekend with the running of the Grand National.

It turns out that bookmakers are using entropy when they decide their odds, albeit unknowingly.

Imagine a greyhound race in which there are six dogs running. The bookmaker assigns each dog an equal probability of winning – one-sixth – and thus each dog has odds of six to one (6-1).

The problem for the bookmaker is that I can come along and bet an equal sum of money on each dog and be guaranteed to recoup my total bet, the bookmaker cannot make any money.

With longer odds the problem is even worse:

Now the bookmaker is guaranteed to lose money. A £1 bet on each dog for a total of £6 yields a win of £7 no matter what the result.

The obvious answer is to shorten the odds:

Now the bookmaker is guaranteed to make money, provided that all bets are evenly distributed amongst the dogs. If I bet £1 on each dog again, I can only win £5 and the bookmaker is guaranteed a £1 profit.

But this isn’t realistic: in real life there is usually a ‘favourite’, a dog generally considered more likely to win than the others. Therefore the bookmaker has to offer shorter odds on this dog, in order to avoid losing too much money and the opposite is true for the dog least least likely to win – the bookmaker can offer longer odds because he’s less likely to have to pay out.

But how can a bookmaker be sure that his odds have been calculated correctly? How can he be sure that, no matter what the outcome, he doesn’t end up too much out of pocket?

Which set of odds should he offer? The green or the purple? The green will result in paying out less money, but the purple will entice more customers to place a bet.

Unconciously, the bookmaker is calculating the entropy of the system. The more disordered the system, the greater its entropy. The greater its entropy, the greater the reward for the bookmaker. With six dogs each at odds of 6-1 the entropy is exactly 1. With the dogs at 5-1 the entropy is greater than 1; and at 7-1 the entropy is less than 1.

If we make a number of assumptions about how people bet, we can analyse the odds and calculate whether or not the bookmaker will make a profit.

  1. A dog’s odds are related to its chance of winning.
  2. Bets will be distributed amongst dogs according to their odds (e.g. people are more likely to bet on the favourite).
  3. With longer odds, more people will bet.

The green odds yield an entropy of 1.157 and the purple odds yield an entropy of 0.900. Assuming each person bets £1 and that 49 people will bet on the green odds and 55 on the purple odds the bookmaker can expect to make $6.66 on the green odds and lose £6.08 on the purple odds.

You can check yourself using the downloadable odds calculator (23.5kB, Excel). Change the bold values to check the outcome.

I worked it out and the Grand National’s entropy was 1.398, so Bookmakers should be happy!

How big is your telescope?

I love the way that scientific institutions name telescopes.

It started with the “large” telescopes like the Large Binocular Telescope (LBT), an 11.4-metre telescope (made up of two 8.4-metre mirrors) in the Pinaleno Mountains of Arizona; the Very Large Telescope (VLT), an array of four 8.2-metre telescopes in the Atacama Desert in Chile; and the Large Synoptic Survey Telescope (LSST), an 8.4-metre telescope also planned for the Chilean desert.

Large Binocular Telescope

Very Large Telescope array

After the “large” telescopes came the “giant” telescopes: the Giant Magellan Telescope (GMT) is 21.4-metre telescope under construction at the Las Campanas Observatory in Chile and the Giant Segmented Mirror Telescope (GSMT) is a planned 20-30-metre telescope.

Giant Magellan Telescope

Some of the larger telescopes, like the Thirty Metre Telescope (TMT) have disappointingly simple names; it seems that after using up “giant” they went back to “large”, but with adverbs. There’s the 42-metre European Extremely Large Telescope (EELT) and my personal favourite: the 60-metre Overwhelmingly Large Telescope (OWL).

Thirty Metre Telescope

European Extremely Large Telescope

Overwhelmingly Large Telescope