1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Int64.From: RoundingMode

Discussion in 'Power Pivot, Power Map etc' started by Chihiro, Jul 31, 2018.

  1. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    Can those of you that have PowerQuery test following? I'm seeing unexpected result when using Int64.From() with third argument for RoundingMode.

    In sheet:
    1. List numbers from -2 to 2 increment number by 0.25 each row (header: Num).
    2. In next column enter =INT(A2) and copy down. (header: Int)
    3. Load to PQ

    In PQ Editor:
    Add custom columns with following formulas.
    Code (vb):
    =Int64.From([Num],null,RoundingMode.Down)
    Code (vb):
    =Int64.From([Num],null,RoundingMode.ToEven)
    Code (vb):
    =Int64.From([Num],null,RoundingMode.TowardZero)
    Code (vb):
    =Int64.From([Num],null,RoundingMode.Up)
    What result do you get? Does all custom columns return same result? Or different?
  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    Clearly different. Also the formula typed and the round down/up action via the ribbon button are returning different results.
    upload_2018-8-1_15-28-31.png
    Chihiro likes this.
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,824
    Thanks, looks like Int64.From() will never return same result as INT() function.

    And RoundingMode argument in this function, only impacts how x.5 is treated. Not all decimals.
    Ex.
    =Int64.From("1.50000001", RoundingMode.Down)
    Will return 2, and same for .TowardZero etc.

    =Int64.From("1.5", RoundingMode.Down)
    Will return 1, but .ToEven will return 2.

    On my original test, I didn't notice this as I didn't have number that fell exactly at x.5.

    And yes, to simulate how Excel's INT() function works, you'd use Number.RoundDown. Not Number.Round() with RoundingMode argument.

    Similar issue can be observed if you compare Excel's MOD function with Number.Mod.

    To simulate Excel in PQ for MOD. You'd need something like below.

    Excel: MOD(n,d) = n-d*INT(n/d)

    PQ: =n-d*Number.RoundDown(n/d)
    GraH - Guido likes this.

Share This Page