I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round function which I found to be returning 0 for 0.5 rounded to integer whereas the worksheet round function returns 1. Strangely, the VBA round function returns 2 for 1.5. I had to substitute the worksheet function in place of the VBA one.
我有一个应用程序返回最大匹配值到一个大的值集合中的某些值（如我之前的问题），我选择了一个VBA解决方案。在使用上述申请的过程中，我观察到0.5值的结果不正确。我一直在使用VBA Round函数，我发现它返回0表示0.5舍入到整数，而工作表round函数返回1.奇怪的是，VBA round函数返回2表示1.5。我不得不用工作表函数代替VBA函数。
Am I missing something?
我错过了什么吗？
3 个解决方案
#1
3
It's a known issue. The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here:
这是一个众所周知的问题。 VBA Round（）函数使用Banker的舍入，而电子表格单元格函数使用算术舍入。查看详细信息：
PRB: Round Function different in VBA 6 and Excel Spreadsheet
PRB：圆形函数在VBA 6和Excel电子表格中不同
The workaround proposed by Microsoft is to write a custom function to get the desired results.
Microsoft提出的解决方法是编写自定义函数以获得所需结果。
Banker's rounding always rounds 0.5 to the nearest even number and is standard in accounting, which is why Excel works that way. Arithmetic rounding rounds 0.5 up to the next number.
银行家的舍入总是将0.5舍入到最接近的偶数，并且是会计的标准，这就是Excel以这种方式工作的原因。算术舍入舍入0.5到下一个数字。
#2
2
You can blame Microsoft for this one: http://support.microsoft.com/kb/194983
你可以责怪微软这个：http：//support.microsoft.com/kb/194983
Here is a list of ways around it: http://www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
以下是一系列解决方法：http：//www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
#3
2
Question answered completely here and copied here (by permission of the author :):
问题在这里完全回答并复制到这里（经作者许可:)：
Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
小心，VBA Round函数使用Banker的舍入，它将.5舍入为偶数，如下所示：
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
而Excel工作表功能回合，总是向上舍入.5。
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
我做了一些测试，它看起来像.5向上舍入（对称舍入）也用于单元格格式化，也用于列宽舍入（当使用通用数字格式时）。 '精确显示'标志似乎不会进行任何舍入，它只使用单元格格式的舍入结果。
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
我尝试在VBA中实现Microsoft的SymArith函数进行舍入，但是当你试图给它一个像58.55这样的数字时，发现Fix有错误。函数给出58.5而不是58.6的结果。然后我终于发现你可以使用Excel工作表Round函数，如下所示：
Application.Round(58.55, 1)
Application.Round（58.55,1）
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
这将允许您在VBA中进行正常的舍入，尽管它可能不如某些自定义函数快。我意识到这个问题已经完全循环，但是想要将其包含在内以便完整。
#1
3
It's a known issue. The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here:
这是一个众所周知的问题。 VBA Round（）函数使用Banker的舍入，而电子表格单元格函数使用算术舍入。查看详细信息：
PRB: Round Function different in VBA 6 and Excel Spreadsheet
PRB：圆形函数在VBA 6和Excel电子表格中不同
The workaround proposed by Microsoft is to write a custom function to get the desired results.
Microsoft提出的解决方法是编写自定义函数以获得所需结果。
Banker's rounding always rounds 0.5 to the nearest even number and is standard in accounting, which is why Excel works that way. Arithmetic rounding rounds 0.5 up to the next number.
银行家的舍入总是将0.5舍入到最接近的偶数，并且是会计的标准，这就是Excel以这种方式工作的原因。算术舍入舍入0.5到下一个数字。
#2
2
You can blame Microsoft for this one: http://support.microsoft.com/kb/194983
你可以责怪微软这个：http：//support.microsoft.com/kb/194983
Here is a list of ways around it: http://www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
以下是一系列解决方法：http：//www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
#3
2
Question answered completely here and copied here (by permission of the author :):
问题在这里完全回答并复制到这里（经作者许可:)：
Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
小心，VBA Round函数使用Banker的舍入，它将.5舍入为偶数，如下所示：
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
而Excel工作表功能回合，总是向上舍入.5。
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
我做了一些测试，它看起来像.5向上舍入（对称舍入）也用于单元格格式化，也用于列宽舍入（当使用通用数字格式时）。 '精确显示'标志似乎不会进行任何舍入，它只使用单元格格式的舍入结果。
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
我尝试在VBA中实现Microsoft的SymArith函数进行舍入，但是当你试图给它一个像58.55这样的数字时，发现Fix有错误。函数给出58.5而不是58.6的结果。然后我终于发现你可以使用Excel工作表Round函数，如下所示：
Application.Round(58.55, 1)
Application.Round（58.55,1）
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
这将允许您在VBA中进行正常的舍入，尽管它可能不如某些自定义函数快。我意识到这个问题已经完全循环，但是想要将其包含在内以便完整。