即使浮点数不精确，Excel 如何成功地将它们舍入? [英] How does Excel successfully round floating point numbers even though they are imprecise?

问题描述

例如，这篇博客说 0.005 并不完全是 0.005，但是将这个数字四舍五入会产生正确的结果.

For example, this blog says 0.005 is not exactly 0.005, but rounding that number yields the right result.

我在 C++ 中尝试了各种四舍五入，但在将数字四舍五入到某些小数位时都失败了.例如，Round(x,y) 将 x 舍入为 y 的倍数.所以 Round(37.785,0.01) 应该给你 37.79 而不是 37.78.

I have tried all kinds of rounding in C++ and it fails when rounding numbers to certain decimal places. For example, Round(x,y) rounds x to a 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() for float in C++ 中的解决方案对于以上问题.

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

推荐答案

Round(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 存在合理的争论.如何处理这些极端情况有点武断，也没有一致的答案.微软内部甚至没有一个一致的答案:由于历史原因，Round() 函数在不同的微软产品中没有以一致的方式实现."(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?

原因与实数在计算机中的表示方式有关.与许多其他公司一样，Microsoft 使用 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屋！