Excel中的银行家取整公式 [英] Banker's rounding formula in Excel

问题描述

这是一个众所周知的问题，默认情况下，.NET使用Banker的舍入，将X.5舍入为最接近的偶数.另一方面，Excel使用算术舍入，其中X.5始终被舍入，就像我们大多数人在学校里教过的那样.

It is a well known problem that by default, .NET uses Banker's rounding whereby X.5 is rounded to nearest even number. Excel, on the other hand uses arithmetic rounding where X.5 is always rounded up, just like most of us have been taught at school.

是否可以使用公式在Excel中进行银行家四舍五入?

Is it possible to perform banker's rounding in Excel using a formula?

推荐答案

使用以下公式:

=IF(AND(ISEVEN(A1*10^0),MOD(A1*10^0,1)<=0.5),ROUNDDOWN(A1,0),ROUND(A1,0))

替换所有0，其中有4个，具有所需舍入的重要性.

Replace all the 0, there are 4 of them, with the significance of the desired rounding.

或者您可以创建自己的UDF以使用VBA Round，这是Banker的Rounding:

Or you can create your own UDF to use VBA Round, which is Banker's Rounding:

Function BankerRound(rng As Double, sig As Integer) As Double

    BankerRound = Round(rng, sig)
End Function

那么它就是:

=BankerRound(A1,0)

这篇关于Excel中的银行家取整公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！