Round() bug or limit?

goMiddleton 2017-07-05 01:34:04 UTC #1

Take the number 153.49 and divide by the integer 2000. The result in Panorama X is 0.07674500000000001.

Here are the results of the following round functions.
round(153.49 / 2000,.01) = 0.08
round(153.49 / 2000,.001) = 0.077
round(153.49 / 2000,.0001) = 0.0767
round(153.49 / 2000,.00001) = 0.07675000000000001

Is .0001 the limit of Round() or is this a bug?

admin 2017-07-05 07:56:20 UTC #2

This is caused by the fact that floating point arithmetic is performed using base 2 rather than base 10 arithmetic. Many decimal numbers are not expressible using base 2, so this type of discrepancy occurs. Panorama is using the C compiler to perform arithmetic, which presumably is using the floating point operations available on the Intel CPU chip.

If you do a google search for floating point accuracy you’ll find lots of information about this topic. A couple of post that jump out at me are:

especially this quote about half way down the page:

The fact that the “point” in “floating point” is a binary point and not decimal point has a way of defeating our intuitions. The classic example is 0.1, which needs a precision of only one digit in decimal but isn’t representable exactly in binary at all.

and also this post:

But there are plenty more if you have some time to kill.

goMiddleton 2017-11-18 18:33:29 UTC #3

I was just reading the Help page about Round() and I noticed the last example of rounding a date.

round(startdate,7)

The help file says that the above example will round to the first day of the week. I am wondering how does this work? I am sure it probably really simple and obvious but it is escaping me at the moment…

So if I plugged in todays date into the formula, round(today(),7) I come up with the value 14 which is a Tuesday.

I should note that the result of the formula round(today(),7) is 11/20/17 which is Monday. I am pretty sure I have a preference set some where to treat Monday as the first day of the week instead of Sunday. Im still missing how it came up with 11/20/17.

Thanks

dave 2017-11-18 19:07:50 UTC #4

The round( function doesn’t really have anything to do with dates. It’s for rounding numbers. Dates are numbers, so it will round them, but without regard to which day is the first day of the week. The Julian day numbers for Mondays are multiples of 7, so that’s what that formula will round to.

I’m guessing you wrote that as round("11/18/2017",7) which Panorama would have evaluated as if you had written round(val("11/18/2017"),7). Val(“11/18/2017”) is 11, because the val( function reads up until it reaches the first character that doesn’t belong in a number, and that’s the slash. 11 is nearer to 14 than it is to 7, so it rounds to 14.

I guessed wrong. round("11/18/2017",7) returns an error. You must have actually written, round(val("11/18/2017"),7).

goMiddleton 2017-11-18 19:39:25 UTC #5

Hi Dave,

Thank you.

I was actually writing it on paper. So it looked like this - today is 11/18/17 and if I am going to round that using 7 then it would look like the example in the help file for dozen, 12, 24, 36. So I mistakenly thought the date would look like, 7, 14, 21, 28. I did not remember to consider that the number for the date 11/18/17 is 2458076. So using round(2458076,7) the result is 2458078 and with datepattern(2458078,“MM/DD/YY”) the result is 11/20/17. The other mistake I made was I assumed round() always rounds down.

It is still not clear to me though how the result of round(2458076,7) is 2458078.

Actually I think I understand. If I divide 2458076 by 7 the result is 351153.714285714285714 so the round() function will look for the next evenly divisible value, in this case it increases the number to 2458078 which when divided by 7 = 351154.

Am I correct?

One more quick question, what criteria does round() use to decide when to round up versus rounding down?

dave 2017-11-18 19:49:29 UTC #6

Correct. In Panorama X, the round( function is a custom function, defined by

ROUND+2((int((•1/•2)+0.5))*•2

so it divides 2458076 by 7 getting 351153.714285714285714, adds 0.5 to get 351154.214285714285714, truncates that to 351154 and multiplies by 7 to get 2458078.

goMiddleton 2017-11-18 19:52:44 UTC #7

Thank you Dave for bring clarity to my cluttered mind.

I really appreciate all of your posts. I have learned much from your expertise.