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

Groups > comp.databases.filemaker > #2842

Re: "Total of" Rounding Error?

Show all headers | View raw

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.

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.

Charlie
-- 
Nobody in this country got rich on his own.  You built a factory--good.
But you moved your goods on roads we all paid for.  You hired workers we
all paid to educate. So keep a big hunk of the money from your factory.
But take a hunk and pay it forward.  Elizabeth Warren (paraphrased)

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

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