Why is Numbers (the spreadsheet app) for ios doing this?
June 1, 2016 7:28 AM Subscribe
I use a Numbers spreadsheet on my iPhone 5S to keep track of my mileage for work. Sometimes -- but not always -- when I do a calculation, it gives me an almost-accurate but borked up number.
In one column, I put the mileage on the trip meter when I start driving for the day. In the next column, I enter the mileage when I stop driving. The third column is the difference between the starting and stopping mileage. So if I start at 121.3 and stop at 131.4, the third column should be 10.1. Sometimes when I make this calculation, though, it shows the result as 10.00000000000009 or 10.100000000000002 or something like that. Here's a screenshot so you can see what I'm talking about.
Why? I don't ever change any of the settings or formatting. Sometimes I'll do several calculations in a row and only one of them will turn out weird. How can I make this stop happening?
In one column, I put the mileage on the trip meter when I start driving for the day. In the next column, I enter the mileage when I stop driving. The third column is the difference between the starting and stopping mileage. So if I start at 121.3 and stop at 131.4, the third column should be 10.1. Sometimes when I make this calculation, though, it shows the result as 10.00000000000009 or 10.100000000000002 or something like that. Here's a screenshot so you can see what I'm talking about.
Why? I don't ever change any of the settings or formatting. Sometimes I'll do several calculations in a row and only one of them will turn out weird. How can I make this stop happening?
Best answer: Floating Point Arithmetic is the culprit and it is an issue in Excel as well.
posted by soelo at 7:50 AM on June 1, 2016 [2 favorites]
posted by soelo at 7:50 AM on June 1, 2016 [2 favorites]
Best answer: This looks like it's due to floating-point math. It's a fairly common problem related to how decimal numbers are represented in binary. Basically, some decimal numbers cannot be represented accurately because we have to tell a computer to represent them by summing a series of 1/2, 1/4, 1/8, etc - we can get very close to some numbers, but not exactly. When you start doing calculations involving these "off by a bit" numbers you start to see issues with loss of accuracy.
I'm not familiar with the app itself, but is it possible for you to change the precision to force fewer decimal points (without truncating)? You may also be able to do something like:
ROUND((subtraction formula),1) to force it to the nearest tenth.
Another solution would be to use whole integers for your numbers, but that would be pretty annoying.
posted by one of these days at 7:51 AM on June 1, 2016 [1 favorite]
I'm not familiar with the app itself, but is it possible for you to change the precision to force fewer decimal points (without truncating)? You may also be able to do something like:
ROUND((subtraction formula),1) to force it to the nearest tenth.
Another solution would be to use whole integers for your numbers, but that would be pretty annoying.
posted by one of these days at 7:51 AM on June 1, 2016 [1 favorite]
Best answer: Not a bug. Standard IEEE floating point math.
Many apps and calculators massage the IEEE result to get rid of the residual error. Apple Numbers does not, they stick with the standard.
Here's a worked example of 1.1 - 1.0 - 0.1 =
The solution is to round the answer, this does have the advantage of forcing you to think about accuracy, if you start with data as a 2 digit number, assuming that the answer will be accurate to 10 decimal places is usually not justified.
posted by Lanark at 8:12 AM on June 1, 2016 [2 favorites]
Many apps and calculators massage the IEEE result to get rid of the residual error. Apple Numbers does not, they stick with the standard.
Here's a worked example of 1.1 - 1.0 - 0.1 =
The solution is to round the answer, this does have the advantage of forcing you to think about accuracy, if you start with data as a 2 digit number, assuming that the answer will be accurate to 10 decimal places is usually not justified.
posted by Lanark at 8:12 AM on June 1, 2016 [2 favorites]
this is why most software that deals with money uses the integer 1 to mean 1 cent, not one dollar. No floating point math issues then.
posted by thelonius at 8:28 AM on June 1, 2016
posted by thelonius at 8:28 AM on June 1, 2016
Response by poster: Thanks, everyone! Problem solved!
posted by kitty teeth at 3:54 PM on June 1, 2016
posted by kitty teeth at 3:54 PM on June 1, 2016
This thread is closed to new comments.
posted by jessamyn at 7:49 AM on June 1, 2016