![]() ![]() The second thin g that surprises me is that, at the time I am writing this article, I could not find information about the default rounding mode in Power Query in any of Microsoft ‘ s documentation for the tool or anywhere in the user ‘ s interface as shown above. VBA also uses the Banker ‘ s Rounding method, as explained in Bill Jelen ‘ s video mentioned above. ) However, and to be fair, I need to mention that inconsistency in rounding methods already existed before Power Query did. The f i rst one is the default rounding mode in Power Query not being the same as in Excel ( the users expect consistency throughout the tool. T wo things surprise me about the choices made by the Power Query team regarding the Number.Round function. I can understand that there are good reasons to choose the “ Banker ‘ s R ounding “ method over the “ Traditional “ one as the rounding default method, but certainly there are scenarios where this rounding method makes no sense. The “ traditional rounding method “ corresponds to RoundingMode.AwayFromZero in Power Query. The Banker ‘ s Rounding Method, also known as ASTM E29 Rounding, corresponds to the default rounding method used by Power Query : RoundingMode.ToEven. To avoid that and minimize the difference between the real value and the value obtained after summing up many round ed numbers, we can adopt the “ Banker ‘ s Rounding Method “ that Bill Jelen explained in this video in 2009. In a business scenario, for example, where we do this rounding process thousands or millions of times, we may end up with a final result that may not be in the organization’s best interest. But if we think carefully, the rounding method that always rounds up in the cases where rounding up or down would s k ew the original value in the same absolute amount, will con tinuous ly add up positive errors for similar rounding situations. ![]() When we round numbers, we introduce an error to our calculations when comparing the final results we get to the one we would get calculating with the real and exact original values.Įxcept for very rare and special occasions, I always use the Excel ROUND( ) method and never questioned this rounding procedure much. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |