What is wrong with this simple maths equation?

Questions
1

I can't see why the equal is failing in this equation. Can anyone help?

/* 
GST = 10% sales tax
WET = 29% liquor tax
*/

Let ( [
  retail = 24.99
; exgst = retail * 10 / 11
; gst = exgst * 1 / 10
; exwet = exgst * 100 / 129
; wet = exWet * 29 / 100
; recalc = exwet * 1.29 * 1.1
];

List ( 
  "original value: " & retail 
; "recalculated value: " & recalc 
; "Are they equal ? " & if ( recalc = retail ; "yes" ; "no" ) 
; "Are they exact? " & if ( Exact ( recalc ; retail ) ; "yes" ; "no" )
)

)

result: 
original value: 24.99
recalculated value: 24.99
Are they equal ? no
Are they exact? yes
1 Like
2

Great one, @Malcolm .

I get the same result in my data viewer.

No good explanation, but a few observations:

  • These cases return True:
GetAsText( recalc ) = retail 
recalc = GetAsText( retail )
GetAsText( recalc ) = GetAsText( retail )
  • This returns False:

GetAsNumber( recalc ) = GetAsNumber( retail )

  • Wrapping operations in SetPrecision reveals a very small delta between the two values.

It's as though, despite what I think the behavior should be, that tiny delta is still affecting the = comparison operation, unless some extra action happens which causes FMP to cast the values to strings/text.

That's just speculation, of course, and, I'll add: It does not feel consistent with what I am used to, nor what I would expect...

1 Like
3

@steve_ssh is onto something. For now, if you want them to be equal, you need to compare them using Round, with a precision set to 17 or below. Precision above 18 makes them inequal.

I do not use SetPrecision often, and would need to dig deeper to figure what happens here.

The basic math would want the operations to cancel each other, but FileMaker applies them in sequence, and it seems something is kept along the way, something FileMaker is not outputting in the result it gives back.

2 Likes
4

I know that dividing by prime numbers will cause rounding errors, so I expected to see something funky happening.

I tried SetPrecision at a variety of levels: 16, 32, 48, 56, 64. At 56 I saw the recalc value expressed as 24.99000000000000000000000000000000000000000000000000000001. For all the other values, the outcomes were expressed as two decimal places.

The big difference was that the equality test was true when I used SetPrecision (except at 56) to perform the calculations. Even though the visual output was identical. I think this is pointing toward your speculation that the fractional difference is being preserved somewhere.

As GetAsNumber comparison generates a false result, which is what we see in the first example. And because the GetAsText comparisons all generate a true result I don't think that FMP is casting the values to strings. I think that it is treating them as numbers.

1 Like
5

It's worth noting that Exact() only compares text values.

5 Likes
6

@Bobino, I agree that @steve_ssh is on the right track.

You are right that forcing the expression engine to use a specific level of rounding, via Round or SetPrecision, does affect the outcome. Even so, there is a trick in that.

The output generated SetPrecision differs at each level. At some higher levels it will resolve to two decimal places.

And the output of Round only affects the number if it needs to be truncated.

Here is x with 48 decimal places:

x = 24.989999999999999999999999999999999999999999999999 
Round ( x ; 48 )  => 24.989999999999999999999999999999999999999999999999

Here is x with 49 decimal places:

x = 24.9899999999999999999999999999999999999999999999999 
Round ( x ; 48 )  => 24.99

and at 3 decimal places:

round( 24.989 ; 4 ) => 24.989

And here is the calculation engine working at several decimal places beyond the limit used in the SetPrecision function

/* 
GST = 10% sales tax
WET = 29% liquor tax
*/

Let ( [
  retail = 24.99 
; gstRate = 1 / 10
; wetRate = 29 / 100
; exgst = SetPrecision ( retail / ( 1 + gstRate ) ; 18 )
; gst = SetPrecision ( exgst * gstRate ; 18)
; exwet = SetPrecision ( exgst / ( 1 + wetRate ) ; 18 )
; wet = SetPrecision ( exWet * wetRate ; 18 )
; recalcMultiply = SetPrecision ( exwet * (1 + wetRate ) * ( 1 + gstRate ) ; 299 ) 
; recalcAddition = SetPrecision ( exwet + wet + gst ; 299 )
];

list( 
recalcMultiply
;recalcaddition
)
)

Result:
24.989999999999999999628 < 21 decimal places
24.989999999999999999   < 18 decimal places
1 Like
7

I had another problem today where a report was just off by a penny using regular multiplication with values like 42.75. Had to use ROUND().

8

IDK if this is your specific error but in some cases you capitalize W in exWet and in other cases you don't.

9

Sharp eyesight @JasonMark! Fortunately (?) the equation editor isn't case sensitive.

The basic problem is that we when we assign a value to a variable the onscreen representation of that value is misleading.

In this case the variable is storing an infinite fraction, 24.9899999999...... truncated at 16 decimal places. However, when I ask what the value is I am shown 24.99. When I use SetPrecision to show the value it continues to show 24.99 - sometimes even when I ask for precision at the maximum of 400 decimal places.

The flow-on effect is that comparisons using = , < , and > will produce the wrong result. It is possible that it will create problems.

2 Likes
10

Never use "=" to compare floating point types.

I typically use <= or >=.

Or also -- test a difference between an observed value and a desired value being less than some "delta" for an IF statement to be true.

3 Likes
11

That’s my favorite approach, giving the acceptable “error” specifying greater or less or greater/less

I wonder how many times stem teachers have seen :microbe: instead of :small_red_triangle:

1 Like