We get a lot of questions about surprising behavior when numbers have digits after the decimal point. Sometimes numbers don't look right, sometimes they don't seem to behave right mathematically. Often someone thinks they've found a bug, and it's difficult to explain how the behavior is normal and that nearly all computer software shares the same behavior.
As a software QA guy with a mathematics background (see my introduction in the second paragraph of my first Quick Base community post), I find this kind of question particularly interesting. Let me explain a little bit about how computers represent numbers and do math. Then I'll show a few common questions that people ask and walk through explanations - and workarounds, where possible. A quick note: most of the examples discussed are around formulas. That is just because they are usually easier to illustrate with. The same caveats and information applies to any part of Quick Base where you are comparing numbers. This includes, but is not limited to: report filters, custom data rules, permissions, etc.
Let me explain the two main things that are going on with this standard for computer mathematics.
If you are already familiar with this, skip down to the next bullet ("It's easy to forget that displayed decimals and actual precision are different things.") If you are interested in some more context, let's dig into this a little more.
It's easy to forget that displayed decimals and actual precision are different things. Most software applications allow you some way to choose how many digits you wish to display after the decimal point. Many systems automatically choose to display fewer digits than would be possible when the value is very close to a short value. For example, if you have the number 0.3499999999999999, many systems will automatically choose to display this value as "0.35".In Quick Base, if you go to the field properties page for a numeric field, you'll find in the "Display" section a setting called "Decimal places". Remember that this is only changing the maximum number of digits the application uses to show you the approximate value - it does not change the actual underlying value.Bringing that back to Quick Base, let's combine both of the above concepts. We can look at a scenario where we key in one of the above numbers, like this shown below. Rounded off, this "looks like" it is .10 - but it really isn't.
So now that you're picturing computer arithmetic as being on a very (but not infinitely) precise yardstick, and now that you're keeping in mind that displayed decimals is different from mathematical precision, let's get into some typical questions and discuss workarounds.
I have a formula that does some math and the computer's getting the last digit wrong. What's up with that? Is that a problem?
Workaround: If you're only concerned about how the number looks, this is a great place to use the "Displayed decimals" property of the field. Say you reduce the displayed decimals of the result to eight digits. Quick Base will (in a manner of speaking) round off the answer to .10000000, recognize it does not need to display the trailing zeroes, and display the number as "0.1".
If you're concerned about how the number behaves mathematically, keep reading.
If ( [Cost] = 1.1, "Yes", "No" ), in a formula, you might consider saying If ( [Cost] > 1.09999 and [Cost] < 1.10001, "Yes", "No" )
If ( [Cost] = 1.1, "Yes", "No" )
If ( [Cost] > 1.09999 and [Cost] < 1.10001, "Yes", "No" )
If ( ROUND([Cost],.00001) = ROUND(1.1,.00001), "Yes", "No" )
When I round a number to a particular decimal place, it's not handling the "point fives" consistently or correctly. Why is that?
(For example, if you're rounding to two decimal places, you might notice that 0.265 rounds up to 0.27, and 0.275 rounds up to 0.28, but 0.285 rounds down to 0.28.)
This is another side effect of the fact that the computer stores fractional numbers in binary, not decimal. That number that looks like 0.265 when you display it in decimal might actually be just a tiny bit more, so it rounds up. That number that looks like 0.285 might actually be just a tiny bit less, so it rounds down.
Workaround: The general strategy here is to round numbers as late as possible, to as many digits as possible.One example we've seen a few times now is when someone is computing a unit price for a large order. Some math gets done that comes up with a small price per item, that looks like it's got exactly half a cent in it (like the 0.285 example above). The application developer rounds this rate to the nearest cent before multiplying the number of units. The business owner expects this to be 0.29 cents per unit, but Quick Base computes it as 0.28 cents per unit, and the one-cent difference times tens of thousands of units comes up to a hundreds-of-dollars "discrepancy".In this case, we suggest that you don't round the unit rate to two digits. Consider rounding it to three or four digits, or even not rounding it at all and just displaying it to three or four digits, and then round the price after you multiply by the number of units.
When I display a number to a particular number of decimal places, sometimes the last digit is wrong. Sometimes it's different from what I get when I round the number to the same number of decimal places. What's happening?
I have a custom key field (or I'm trying to merge on a numeric field). I'm getting duplicate entries. What's the problem?