使用INT功能时的舍入误差 [英] Rounding error when using INT function

问题描述

我有两个单元格中的用户输入，名为UpperRangeHigh和UpperRangeLow。我有以下代码：

I have user input in two cells, named "UpperRangeHigh" and "UpperRangeLow". I have the following code:

dRangeUpper = [UpperRangeHigh] - [UpperRangeLow]
lLines = Int(dRangeUpper * 100 / lInterval)

用户分别将120.3和120输入到输入单元格中。 lInterval的值为10. VBA为lLines而不是3生成2的结果。

The user inputs 120.3 and 120 into the input cells respectively. lInterval has the value 10. VBA produces the result of 2 for lLines, instead of 3.

我可以通过向dRangeUpper添加0.000000001来克服这个问题，但我想知道如果有这个行为的已知原因？

I can overcome this problem by adding 0.000000001 to dRangeUpper, but I'm wondering if there is a known reason for this behaviour?

推荐答案

这似乎是Excel的计算和有效数字的问题。如果你这样做：

This appears to be a problem with Excel's calculation and significant digits. If you do:

= 120.3 - 120 并格式化单元格以显示15位小数，结果显示为：

=120.3 - 120 and format the cell to display 15 decimal places, the result appears as:

0.2999999999999970

这解释了Excel如何使用二进制算术，这可能导致结果与预期的不同：

Here is a brief overview which explains how Excel uses binary arithmetic and that this may result in results divergent from what you would expect:

http://excel.tips.net/T008143_Avoiding_Rounding_Errors_in_Formula_Results.html

您可以通过强制圆角精度，例如10位小数位数：

You can overcome this by forcing a rounded precision, e.g., to 10 decimal places:

lLines = Int（Round（dRangeUpper，10）* 100 / lInterval

这篇关于使用INT功能时的舍入误差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！