Excel如何成功舍入浮动数字，即使它们不精确吗？ [英] How does Excel successfully Rounds Floating numbers even though they are imprecise?

问题描述

例如，

This blog says 0.005 is not exactly 0.005 but rounding that number yields the right result.

我在我的C ++中尝试了所有类型的Round，我没有将数字四舍五入到某些小数位。例如，Round（x，y）将x舍入为y的倍数。所以Round（37.785,0.01）应该给你37.79而不是37.78。

I tried all kinds of Round in my C++ and I have failed for rounding numbers to the certain decimal places. For example, Round(x,y) rounds x to the multiple of y. So Round(37.785,0.01) should give you 37.79 and not 37.78.

我重新打开此问题，要求社群提供帮助。问题是如何解决这个问题呢？

I am reopening this question to ask the community for help. The problem is with the impreciseness of floating point numbers (37,785 is represented as 37.78499999999).

问题是如何解决这个问题？
$ b

这个 round（）for C ++中的解决方案

The question is how does Excel get around this problem?

回合（37.785,0.01）应该给你37.79而不是37.78。

THe solution in this round() for float in C++ is incorrect for the above problem.

推荐答案

首先，没有共识，37.79而不是37.78是正确的答案在这里？连接断路器总是有点艰难。

First off, there is no consensus that 37.79 rather than 37.78 is the "right" answer here? Tie-breakers are always a bit tough. While always rounding up in the case of a tie is a widely-used approach, it certainly is not the only approach.

其次，这个不是

Secondly, this isn't a tie-breaking situation. The numerical value in the IEEE binary64 floating point format is 37.784999999999997 (approximately). There are lots of ways to get a value of 37.784999999999997 besides a human typing in a value of 37.785 and happen to have that converted to that floating point representation. In most of these cases, the correct answer is 37.78 rather than 37.79.

附录

考虑下列Excel公式：

Addendum
Consider the following Excel formulae:

=ROUND(37785/1000,2)
=ROUND(19810222/2^19+21474836/2^47,2)

两个单元格都将显示相同的值，37.79。对于37785/1000是否应该以两个位置精度应该达到37.78或37.79，存在一个合理的争论。如何处理这些角落的情况是有点任意，并没有一致的答案。微软甚至没有一个共识的答案： （），Round（）函数不是以一致的方式在不同的Microsoft产品中实现。 support.microsoft.com/kb/196652\"> http://support.microsoft.com/kb/196652 ）给定一个无限精密机器，微软的VBA将从37.785到37.78（银行家的回合），而Excel将屈服37.79（对称算术轮）。

Both cells will display the same value, 37.79. There is a legitimate argument over whether 37785/1000 should round to 37.78 or 37.79 with two place accuracy. How to deal with these corner cases is a bit arbitrary, and there is no consensus answer. There isn't even a consensus answer inside Microsoft: "the Round() function is not implemented in a consistent fashion among different Microsoft products for historical reasons." ( http://support.microsoft.com/kb/196652 ) Given an infinite precision machine, Microsoft's VBA would round 37.785 to 37.78 (banker's round) while Excel would yield 37.79 (symmetric arithmetic round).

后面公式的舍入没有参数。它严格小于37.785，所以应该到37.78，而不是37.79。然而Excel把它凑了起来。为什么？

There is no argument over the rounding of the latter formula. It is strictly less than 37.785, so it should round to 37.78, not 37.79. Yet Excel rounds it up. Why?

原因与计算机中如何表示实数有关。微软，像许多其他，使用IEEE 64位浮点格式。当以这种格式表示时，数字37785/1000受到精度损失。这种精确损失不发生在19810222/2 ^ 19 + 21474836/2 ^ 47;它是一个精确数字。

The reason has to do with how real numbers are represented in a computer. Microsoft, like many others, uses the IEEE 64 bit floating point format. The number 37785/1000 suffers from precision loss when expressed in this format. This precision loss does not occur with 19810222/2^19+21474836/2^47; it is an "exact number".

我有意构造的精确数字具有与不完全37785/1000相同的浮点表示。 Excel确定Excel的 ROUND（）函数是如何工作的：这是一个对称算术舍入的变体。

I intentionally constructed that exact number to have the same floating point representation as does the inexact 37785/1000. That Excel rounds this exact value up rather than down is the key to determining how Excel's ROUND() function works: It is a variant of symmetric arithmetic rounding. It rounds based on a comparison to the floating point representation of the corner case.

C ++中的算法：

#include <cmath> // std::floor

// Compute 10 to some positive integral power.
// Dealing with overflow (exponent > 308) is an exercise left to the reader.
double pow10 (unsigned int exponent) { 
   double result = 1.0;
   double base = 10.0;
   while (exponent > 0) {
      if ((exponent & 1) != 0) result *= base;
      exponent >>= 1;
      base *= base;
   }
   return result;
}   

// Round the same way Excel does.
// Dealing with nonsense such as nplaces=400 is an exercise left to the reader.
double excel_round (double x, int nplaces) {
   bool is_neg = false;

   // Excel uses symmetric arithmetic round: Round away from zero.
   // The algorithm will be easier if we only deal with positive numbers.
   if (x < 0.0) {
      is_neg = true;
      x = -x; 
   }

   // Construct the nearest rounded values and the nasty corner case.
   // Note: We really do not want an optimizing compiler to put the corner
   // case in an extended double precision register. Hence the volatile.
   double round_down, round_up;
   volatile double corner_case;
   if (nplaces < 0) {
      double scale = pow10 (-nplaces);
      round_down  = std::floor (x / scale);
      corner_case = (round_down + 0.5) * scale;
      round_up    = (round_down + 1.0) * scale;
      round_down *= scale;
   }
   else {
      double scale = pow10 (nplaces);
      round_down  = std::floor (x * scale);
      corner_case = (round_down + 0.5) / scale;
      round_up    = (round_down + 1.0) / scale;
      round_down /= scale;
   }

   // Round by comparing to the corner case.
   x = (x < corner_case) ? round_down : round_up;

   // Correct the sign if needed.
   if (is_neg) x = -x; 

   return x;
}   

这篇关于Excel如何成功舍入浮动数字，即使它们不精确吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！