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

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.
(written 2009-11-21) 249c

Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles

U102 - Lua - Numbers and the Math library.
  [3917] BODMAS - the order a computer evaluates arithmetic expressions - (2012-11-09)
  [2345] Automatically initialising Lua variables the first time - (2009-08-10)
  [1744] Lua examples, Lua Courses - (2008-08-08)
  [1699] If you are learning Lua, here are some more examples - (2008-07-06)

Q110 - Object Orientation and General technical topics - Programming Algorithms
  [3662] Finding all the unique lines in a file, using Python or Perl - (2012-03-20)
  [3620] Finding the total, average, minimum and maximum in a program - (2012-02-22)
  [3451] Why would you want to use a Perl hash? - (2011-09-20)
  [3102] AND and OR operators - what is the difference between logical and bitwise varieties? - (2010-12-24)
  [3093] How many toilet rolls - hotel inventory and useage - (2010-12-18)
  [3072] Finding elements common to many lists / arrays - (2010-11-26)
  [3042] Least Common Ancestor - what is it, and a Least Common Ancestor algorithm implemented in Perl - (2010-11-11)
  [2993] Arrays v Lists - what is the difference, why use one or the other - (2010-10-10)
  [2951] Lots of way of converting 3 letter month abbreviations to numbers - (2010-09-10)
  [2894] Sorting people by their names - (2010-07-29)
  [2617] Comparing floating point numbers - a word of caution and a solution - (2010-02-01)
  [2586] And and Or illustrated by locks - (2010-01-17)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2189] Matching disparate referencing systems (MediaWiki, PHP, also Tcl) - (2009-05-19)
  [1949] Nuclear Physics comes to our web site - (2008-12-17)
  [1840] Validating Credit Card Numbers - (2008-10-14)
  [1391] Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14)
  [1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
  [1157] Speed Networking - a great evening and how we arranged it - (2007-04-21)
  [642] How similar are two words - (2006-03-11)
  [227] Bellringing and Programming and Objects and Perl - (2005-02-25)
  [202] Searching for numbers - (2005-02-04)

Back to
Community Area Partnership - inaugural AGM

Previous and next
or
Horse's mouth home

Forward to
The music of the stock market

Some other Articles

Wiltshire Council purchases Melksham Station Land
Kim of the Castle
The story of the railway land at Melksham Station
The music of the stock market
A life lesson from the accuracy of numbers in Excel and Lua
Community Area Partnership - inaugural AGM
Admission
Good example of recursion in Python - analyse an RSS feed
I almost put the bins out this morning
Learning to program in ...

4106 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83 at 50 posts per page

3d5e
This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Public Training Courses

Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??