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

问题描述

例如，

这个博客说，0.005并不完全是0.005，而是四舍五入数字产生正确的结果。

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

我在C ++中尝试过各种各样的Round，而且我将数字舍入到某个小数位。例如，Round（x，y）将x向x的倍数舍入。所以（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.

我正在重新打开此问题，要求社区寻求帮助。问题是浮点数不精确（37,785表示为37.78499999999）。

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).

问题是Excel如何解决这个问题？

The question is how does Excel get around this problem?

在这个 round（）中的C ++中的float的解决方案对于上述问题，不正确。

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

推荐答案

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

"Round(37.785,0.01) should give you 37.79 and not 37.78."

首先，没有一个共识，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.

其次，这个不是打破局面。 IEEE binary64浮点格式的数值为37.784999999999997（约）。除了人类输入值37.785之外，还有很多方法可以获得37.784999999999997的值，并且恰好将其转换为该浮点表示。在大多数情况下，正确的答案是37.78而不是37.79。

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，有两个准确性，这是一个合理的论据。如何处理这些角落的情况有点随意，也没有一致的答案。在Microsoft中甚至没有一个共识答案：（ 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屋！