locked
Exceeding the Range of Number - Bug? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    If I subtract 1 from the Number.Epsilon ("the smallest possible number"), the expression evaluates to -1. It's as though the 'Number.Epsilon' part of the expression is ignored. Is this expected behavior or a bug? 

    = Number.Epsilon - 1 // evaluates to -1

    At the binary level, if executed directly, the above should produce a numeric overflow. However, per Power Query's language spec, section 6.9.1, numeric overflows aren't possible. Instead, values too large or too small to be represented are to be returned as #infinity or -#infinity, respectively. Based on this, shouldn't the above return -#infinity?

    Thanks,
    Ben


    Tuesday, July 3, 2018 10:42 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ben. Number.Epsilon is the smallest possible positive number. It's so small that when you subtract one from it, the double-precision floating point scheme (which is inherently imprecise) can no longer represent the -1 portion as well as the teeny-tiny-waaaaaay-out-there-to-the-right part (which is encoded as an exponent), and so the teeny-tiny portion gets rounded away.

    Ehren

    Friday, July 6, 2018 12:03 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Ben, I guess that it's been a "bug" in Excel for over 20 years, since Excel also returns -1. :)  But what is your logic in thinking that a number so close to 0 minus 1 would be closer to -infinity than -1?  

    On positive and negative infinities, the documentation is pretty clear"

    "Positive infinity (#infinity) and negative infinity (-#infinity). Infinities are produced by such operations as dividing a non-zero number by zero."

    Wednesday, July 4, 2018 12:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Ben. Number.Epsilon is the smallest possible positive number. It's so small that when you subtract one from it, the double-precision floating point scheme (which is inherently imprecise) can no longer represent the -1 portion as well as the teeny-tiny-waaaaaay-out-there-to-the-right part (which is encoded as an exponent), and so the teeny-tiny portion gets rounded away.

    Ehren

    Friday, July 6, 2018 12:03 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks, Ehren & Colin! I was thinking of Number.Epsilon as the furthest number furthest from zero supported by Power BI. Ehren, your pointing out that it is the smallest possible positive number clears a lot up. Thanks again!
    Friday, July 6, 2018 6:31 PM