VBA双对单回合 [英] VBA Double vs Single rounding

问题描述

我的MS Excel中的

我对不同的数据类型有不同的舍入操作行为：

如果是单一的：

 ？ Application.Round（6.575 !, 2）
 6.57 
  

如果Double： p>

 ？ Application.Round（6.575＃，2）
 6.58 
  

有人知道为什么吗？更新： @ paxdiablo 给出了一个很好的答案，所以只是在这里重现：

在单精度中，6.575为0 10000001 101001 0011 0011 0011 0011以二进制表示形式，或6.57499980926513671875。而这轮回到6.57

在双精度增加0011中，他们的重量足以使数字略高于6.575（6.57500000000000017763568394003），因此轮数达到6.58

解决方案

由于浮点精度的不同限制，可能当您使用像 6.575 这样的值时，计算机会选择最接近的近似值。对于单身，这可能是：

  6.574999993 
  

这将圆整。对于双倍，它可能是：

  6.57500000000001 
  

这将会加倍。

---

澄清，IEE754单精度位 - 6.575的价值是：

  s eeeeeeee ffffff ffff ffff ffff ffff f 
 0 10000001 101001 0011 0011 0011 0011 0 
  

（重复 0011 一个无限循环的价值的标志，一个不可靠的代表）。

双倍也非常相似，它有以下位：

  s eeeeeeeeeee ffffff ffff ffff ffff ffff ... 
 0 10000000001 101001 0011 0011 0011 0011 ... 
  

其中也有重复序列（见下文）。

无法正确表示的原因是因为您只能做到这一点，可以通过求和来构建数字鲍威一般来说，你不能总是相信自己打印出来的内容。打印程序知道和调整有限的精度。所以你几乎肯定会得到6.575，因为这是给定位模式最接近的十进制值，但位模式本身将用于计算。

in my MS Excel I have different behavior of rounding operations for different data types:

In case of Single:

? Application.Round(6.575!, 2)
 6.57 

In case of Double:

? Application.Round(6.575#, 2)
 6.58 

Does anybody know why?

---

UPDATE: @paxdiablo gave an excellent answer, so just to reproduce it shortly in here:

In Single precision, 6.575 is 0 10000001 101001 0011 0011 0011 0011 in binary representation, or 6.57499980926513671875. And this rounds to 6.57

In Double precision more 0011 are added and their weight is enough to make the number slightly greater tham 6.575 (6.57500000000000017763568394003), and hence it rounds to 6.58

解决方案

Probably because of the different limits of floating point precision. When you use a value like 6.575, the computer chooses the closest approximation. For a single, this may be:

6.574999993

which will round down. For a double, it may be:

6.57500000000001

which will round up.

---

Clarifying, IEE754 single precision bit-value for 6.575 is:

s eeeeeeee ffffff ffff ffff ffff ffff f
0 10000001 101001 0011 0011 0011 0011 0

(that repeating 0011 at the end is usually the sign of an infinitely recurring value, one not exactly representable).

The double is also very similar, it has the bits:

s eeeeeeeeeee ffffff ffff ffff ffff ffff ...
0 10000000001 101001 0011 0011 0011 0011 ...

which also has the repeating sequence (see below).

The reason it cannot be represented exactly is because you can only do that is the number can be constructed by summing powers of two (like 4, 2, 1/16 and so on) within the number of bits allowed for.

Generally, you cannot always trust what gets printed out since the print routines know about and adjust for the limited precision. So you'll almost certainly get 6.575 since that's the closest decimal value to the given bit pattern, but the bit pattern itself will be used for calculations.

这篇关于VBA双对单回合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！