Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2843

Re: "Total of" Rounding Error?

Show all headers | View raw

On 2017-05-06 15:03:33 +0000, Charles H. Sampson said:
> Helpful Harry <HelpfulHarry@BusyWorking.com> wrote:
>> On 2017-05-02 19:52:41 +0000, Charles H. Sampson said:
>>> 
>>> Is "Total of" succeptible to rounding error? I know this is highly
>>> unlikely, buI have a database in which a "Total of" field is off by one
>>> cent.
>>> 
>>> Here's what I've done so far, beyond challenging my eyesight by staring
>>> at records looking for a one cent error. I've taken two copies of the
>>> database, one where there is no error and a later one where the error
>>> appears. I've exported pertinent fields from each of these databases and
>>> then compared the two files in Word. There are some minor changes, most
>>> of them in comment fields and one insignificant difference of order.
>>> That's all.
>>> 
>>> I know the error has to be mine. I'll keep looking.
>>> 
>>> This is for FileMaker Pro 8.0v3 on a G5 running 10.5.8.
>> 
>> I don't know of any such problem with FileMaker, but I have come across
>> a similar issue in Excel.
>> 
>> I have a spreadsheet which as a double-check has the total of the
>> dollar amounts calculated via two different routes from the same
>> figures, and then a third cell is meant to display a large "X" whenever
>> those totals don't match ... the problem is that it often displays an
>> "X" even when the difference between the two totals shows as "0". A bit
>> of testing found that the two totals do actually differ by something
>> like 0.00000000000001 cents, which is impossible since the figures are
>> all manually entered as normal dollars-and-cents amounts (i.e. 5.60,
>> 50.10, etc.)  :o(
>> 
>> I have been using a FileMaker version of the spreadsheet data as a
>> third checking option and it is doesn't suffer from that issue with
>> that data (and was far quicker than Excel on my now-dead PowerMac G3).
> 
> If I read you correctly, Harry, I might actually be able to help you for
> once -- help you understand what's going on.  I hope I'm not insulting
> your intelligence.
> 
> As you might already know, non-integer values are stored internally in
> computers as approximations, usually as very accurate approximations. So
> 5.60 might be stored internally as something like 5.599999999999 which,
> when displayed to two fractional digits, rounds to 5.60.

It's a computer. There's no need to store values as approximations. In 
fact doing so can take up more storage space than the actual number 
(not that programmers these days care about optimising programs for 
storage and RAM usage).



> A problem occurs when such approximations are used in calculations.
> Suppose you have two different calculation that are mathematically
> equivalent, such as a * (b + c) and a * b + a * c, which mathematically
> give 5.60 as their result. Because of the way the approximate values
> behave when operated on internally,  you might end up with
> 5.599999999999 as one result and 5.600000000001 as the other. Hence,
> values that should match and don't.
> 
> People who work with these approximations know that comparing such
> values for equality is treacherous. What you have to ask instead is,
> "Are the two values approximately equal to within a desired accruracy?"
> For example, instead of displaying "X" when the values are not equal,
> you might want to display "X" when the difference between the two values
> is more than 0.0000001. (Here, "difference" means "the absolute value of
> their difference".)
> 
> As I've said in another post, this is harder to understand if all you're
> doing is totaling a bunch of such numbers.

The real problem is that  Microsoft are utterly hopeless at 
programming, even more so on the Mac, and some computer chips do have 
bugs in their arithmetic (and other) processes.


Helpful Harry   :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

"Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-02 12:52 -0700
  Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-03 09:21 +1200
    Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-06 08:03 -0700
      Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-07 10:41 +1200
        Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-06 19:05 -0700
          Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-07 17:45 +1200
            Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-08 01:22 -0700
              Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-09 09:33 +1200
                Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-09 01:40 -0700
                Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-10 08:56 +1200
                Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-09 16:15 -0700
                Re: "Total of" Rounding Error? dempson@actrix.gen.nz (David Empson) - 2017-05-10 12:16 +1200
                Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-10 13:59 +1200
                Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-10 14:07 +1200
                Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-11 15:09 -0700
                Re: "Total of" Rounding Error? dempson@actrix.gen.nz (David Empson) - 2017-05-12 12:56 +1200
  Re: "Total of" Rounding Error? clkaufmann@gmx.ch (Christoph L. Kaufmann) - 2017-05-04 09:23 +0200
    Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-06 07:42 -0700
  Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-07 19:36 +1200
    Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-08 01:22 -0700
      Re: "Total of" Rounding Error? Helpful Harry <HelpfulHarry@BusyWorking.com> - 2017-05-12 13:36 +1200
  Re: "Total of" Rounding Error? csampson@inetworld.net (Charles H. Sampson) - 2017-05-09 16:04 -0700

csiph-web