Path: csiph.com!aioe.org!.POSTED!not-for-mail From: Helpful Harry Newsgroups: comp.databases.filemaker Subject: Re: "Total of" Rounding Error? Date: Tue, 9 May 2017 09:33:43 +1200 Organization: Aioe.org NNTP Server Lines: 88 Message-ID: References: <1n5edwg.ghrhvsljna3aN%csampson@inetworld.net> <1n5om77.8mi1nlb3lleeN%csampson@inetworld.net> NNTP-Posting-Host: Ubt5gtHZoMhPXof+bZpuVg.user.gioia.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: Unison/2.2 X-Notice: Filtered by postfilter v. 0.8.2 Xref: csiph.com comp.databases.filemaker:2850 On 2017-05-08 08:22:25 +0000, Charles H. Sampson said: > Helpful Harry wrote: > >> On 2017-05-07 02:05:26 +0000, Charles H. Sampson said: >>> Helpful Harry wrote: >>>> On 2017-05-06 15:03:33 +0000, Charles H. Sampson said: >>>>> Helpful Harry 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). >>> >>> I agree that memory is so cheap nowadays that there is no impetus to >>> minimize use of it. However, numbers do need to be stored as >>> approximations. Just as 1/3 cannot be expressed as a finite decimal >>> fraction, there are fractions that cannot be expressed accurately in a >>> computer. >> >> For some numbers you are correct, but simple numbers like >> dollars-and-cents amounts aren't stored as approximations. There's no >> need to do so, which means any rounding errors in totals must be the >> fault of software and/or hardware bugs. > > If it were possible to have a dollars-and-cents field, it would be > tempting to store the value as cents, that is, an integer that is to be > divided by 100 when the value is displayed. e.g., $10.37 would be stored > as 1032. At display time, 1037 would be divided by 100, giving 10 for > the dollars and a remainder of 37, which is the cents. However, there's > no point in doing this because as soon as 1037 is divided by 3, say, > this approach is in a heap of trouble. I think you're confusing two separate things. Numbers themselves on a computer are simply stored as numbers. There aren't stored as approximations. There's no point at all in storing the number 10.37 as an approximation as, say, "10.3662517845" - it would be ridiculously silly to do so. The stored number itself may be an approximation of a *real* *world* number (such as 1/3 or Pi), but that's different. Helpful Harry :o)