FileMaker can't do simple arithmetic?

[HazMatt]

FileMaker Pro 5.0v3, Mac OS 9.2.2

I have 3 fields involved; let's call them: FieldA, FieldB, CalcField

FieldA = number field, with "8.1" entered

FieldB = number field, with "8.0" entered

CalcField = calculation field, whose calculation is: "FieldB - FieldA" and the calculation result is: "-.0999999999999996" it should equal "-.1".

This seems like a simple matter of subtraction, but FileMaker can't seem to get it right. Other numbers with similar decimal occurances don't seem to work either; I get a similar result with a bunch of 9's!

I have also seen this problem happen with other software-based calculators, does that mean anything? Is this just a bug, or what?

[TLangley]

I think I know what your problem is.

On your calculation field, you should select "Calculation result should be a NUMBER."

If you have it set to TEXT then it will give you .0999999996

HTH

Townsend

[Steven H. Blackwell]

It's the way the software interacts with the computer. Try the Round function to clean it up. This si a well known issue. Same thing can happen in Excel.

HTH

Old Advance Man

[LiveOak]

A little more background...

While the representation of 0.1 is exact in decimal arithmetic (as opposed to something like 1/3, which is not), computers perform all their calculations in binary arithmetic (1's and 0's). The binary fraction representation of 0.1 decimal is not exact (0.00011001100... binary).

If you are interested, you can read up a little on binary arithmetic, fixed point and floating point binary numbers, binary algorithms, etc. With a little study the reasons for such errors becomes clear.

-bd

[HazMatt]

Sorry that's the detail I left out. I had it set to "Calculation result is Number". BTW, if I switch it to Text, it still doesn't work.

[danjacoby]

Close, Oak, but not quite.

Computers use logarithms to calculate, and logarithms are often irrational numbers, meaning the computer has to round it off. Occasionally this results in a slight error in calculation.

I got the same result as you. Entering 8.2 instead of 8.1 results in a difference of .199999999999999. Starting with 8.25, however, gets a result of .25. Trying the same thing in MS Excel, I get the exact same result (you might have to set the result cell to show 16 decimal places).

In theory, this shouldn't be a problem, as the answer is correct to 15 significant figures. I suggest you set the format/number of the result field to one or two decimal points; it'll round out just right. Or you can disallow entry in the result field; the long answer gets rounded off when you leave the field.

HTH,

Dan

[LiveOak]

Sorry, Dan, but not true.

Computers use both a fixed point and floating point binary representations for numbers and make selective use logarithms for some functions. All numbers in computers are not represented by their logarithms (how do you add two numbers by using their logarithms?).

Both fixed point representations and mantissas and exponents of floating point representations of numbers have a limited number of digits. In the old days the number of bytes used for a particular representation made it single precision or double precision, etc. Two things are working here: 1) fractional decimal numbers that have exact representations in decimal notation ( e.g. 0.1 decimal) don't have exact non-repeating representations in binary notation and 2) we still have a large by finite number of binary digits being used to represent a number.

-bd (went to college with a slide rule and log tables!)

[HazMatt]

It should be known that I really am not a math person. I just need a FileMaker solution.

My application is calibrating load cells and other things on our products. The decimal point will be very important, as each load cell (ranging from 2 lb to 20,000 lb) will require a different precision (resolution). So, one record might require NO decimal points and another might require 5.

If I revert my fields back to "General Format" in the Number Format options, will it work OK? Do my calculations take the actual number (the calculated "99999999" number), or the number I see in the field?

[The Bridge]

quote:

---

Originally posted by MDLarson:

The decimal point will be very important, as each load cell (ranging from 2 lb to 20,000 lb) will require a different precision (resolution). So, one record might require NO decimal points and another might require 5.

---

I did a preliminary test with three fields:

Number (Number)

Precision (Number)

Number_Display (unstored, text) = NumToText(Int(Round(Number, Precision))) & "." & Left(Substitute(NumToText(Round(Number,Precision)-Int(Round(Number,Precision))),".", "") & "00000", Precision)

I hope it does what you need!

[HazMatt]

quote:

---

Number (Number)

Precision (Number)

Number_Display (unstored, text) = NumToText(Int(Round(Number, Precision))) & "." & Left(Substitute(NumToText(Round(Number,Precision)-Int(Round(Number,Precision))),".", "") & "00000", Precision)

---

Hmm... That's an interesting calculation, but I don't know how I would implement that. I think for now, I'm going to simpy leave the fields formatted as General Format, and not mess with the decimals too much. The display seems to be working OK, and it will simply be up to the operator to input any data with the required decimal points.

[rrrichie]

Try to get rid of the decimals during the calculation... (8.1 * 10 - 8.0 * 10 )/ 10 wil give the right result. More decimals? Just change the 10 to 10000000 etc.

Have fun calculating!

[Garry Claridge]

I am using 5.5v2 on OS X and the calculation works fine; i.e. 8.0 - 8.1 = -.1

Garry

[Korky]

I,m using v5.5 for Windows and it works ok for me too.

[rrrichie]

Click in the result field and the 0,1 will change to 0,09...996.

Also if you multiply (8,1-8,0)*10000000000000 it wil be 99...96 instead of 1 with a lot of zero's. Change the number format of the field to decimal otherwise you get a exponential notation.

As stated before this has nothing to do w/ filemaker, win or mac, just the way computers calculate with decimal numbers. By multiplying the numbers to get rid of the decimal and then doing the calculation will give a better result.

Your basic arithmetic doesn't really get influenced, cause we're talking about errors beyond 15 decimals... But for nitty gritty science stuff..

Greetings Ramon

[agraham999]

I am not sure why everyone is having such a problem with this...if Field A & B are numbers...and the Calc field is b - a...and you make sure that the NUMBER format is "General" your answer is -.1.

Now if you enter the field it will display the answer to 16 decimal points...but if you exit the record...the display is -.1.

Any reason so many people see this differently? Some of us see it correctly and some do not. Hmmm.

[Kurt Knippel]

Actually all this might come down to something that is inherant in microprocessors. They all have an flaw in mathematics. This has somehing to do with the fact that our base 10 system cannot be entirely represented in binary. Programming is needed to take care of this and it does so, but way deep down the number is just so slightly off, and can manifest itself occassionally.

Do not take this as gospel, but I seem to remember this from back in my system-level programming days.

[HazMatt]

Yeah, I figured out early on that "General Format" worked for what I need. It's still frustrating (to me) that a simple arithmetic calculation can be figured out in my mind, but the computer just can't quite get it right. I would expect it to be the other way around.

[danjacoby]

quote:

---

Originally posted by MDLarson:

It's still frustrating (to me) that a simple arithmetic calculation can be figured out in my mind, but the computer just can't quite get it right. I would expect it to be the other way around.

---

A computer was asked the question "Is there a God?" It checked to make sure that it had control of its own power source, then replied, "Now, there is!" -- Isaac Asimov

I'm paraphrasing slightly, but the point is that I'm more relieved than frustrated to know that computers are still stupid. The possibility of self-replicating artificial intelligence scares me; fortunately, we're still a long way from achieving it.

[falkaholic]

I'm have a problem like this it seems.

7% + 7.5% = 14.1532% on OS X and 84.5% on OS 9 (and maybe windows too)

tried 0.07 + 0.075 too. (I inherited the DB, didn't make it.)

[danjacoby]

quote:

---

Originally posted by eric f:

I'm have a problem like this it seems.

7% + 7.5% = 14.1532% on OS X and 84.5% on OS 9 (and maybe windows too)

tried 0.07 + 0.075 too. (I inherited the DB, didn't make it.)

---

Betcha anything you like the OS9 version is either 77% + 7.5%, or 70% + 7% + 7.5%.

[falkaholic]

ya. that what i was thinking. but i've been tooling with it.

but it seems to be even weirder now.

In the customer database it has a checkbox from a value list ("7%" and "7.5%") and have their own fields ("gst" "pst"), which is used in a auto-enter calc:

(customer::gst + customer::pst) * 0.01

that added twist is now, after much hair pulling, only some customers have this problem, some do it right; 0.145, some get .845.

I can't find the correlation of why it would only work sometime.

(Yes, i have tried using "0.07" and "0.075" in the value lists and TextToNum etc, none work)