Main Content

A life lesson from the accuracy of numbers in Excel and Lua

Archive - Originally posted on "The Horse's Mouth" - 2009-11-21 18:31:12 - Graham Ellis

Floating point numbers aren't held EXACTLY in a computer's memory - they're held to a certain accuracy (number of significant figures or binary digits to be more accurante) after which they are truncated or rounded. And this means that at time 1.0 / 3.0 * 3.0 doesn't go full circle and come back to the value one - it comes back to 0.99999999999999 or something like that. Does this matter? Yes - sometimes it does.

The first thing to be aware of is that if you say
if (result == 1.0)
in some langauges, you may get a false result back even if result should - mathematically - equal 1.0.

The second thing to be aware of is how you can loose precision. This came up today on an example that I was writing during the extra Lua course that we had scheduled at the last minute.

Scenario - the value of an asset changes fractionally each day; using 4 byte floats, it's held to 7 significant decimal places and yesterday it was worth 1.634294 pounds per litle and today that's 1.634241 pounds. So that's a loss of 0.000053 pounds, right. Well - yes - it's right, but look at the accuracy of that - I've only been able to quote it to 2 significant figures, and it's showing an error of +- 2%.

The software we were developing was making a further calculation which it compared with that 0.000053, and which had a great deal more accuracy in it and - at times - 'less than' and 'greater than' tests were coming up with different results in Lua to Excel!

Did this matter? No and yes, and no again. If we were simply summing the differences between the two numbers being compared, it wasn't a problem save for the limited accuracy. If we used the "a" is greater or "b" is greater result to make a decision - such as who should be promoted - then we have clearly made to very different decisions whihc could have dramatic consequences. But if the numbers were so close to each other the results, although very different, were by definition virtually identical as to how good they were.

A fascinating subject - and a reminder that there are times that if we are agonisoing over a decision, unsure which of two courses of action are the best ... it might just be that the alternatives are vitually as good as one another and we'ld do better to make a choice - EITHER choice - and get on with handling the results of that decision.