Excel RoundUp与.NET Math.Round区别

Question

在Excel中，=ROUNDUP(474.872126666666, 2) -> 474.88
在.NET中，

Math.Round(474.87212666666666666666666666667, 2, MidpointRounding.ToEven) // 474.87
Math.Round(474.87212666666666666666666666667, 2, MidpointRounding.AwayFromZero) // 474.87

我的客户希望在Excel中进行四舍五入，有没有办法在.NET中获取474.88？

非常感谢。

- nandin

Answer 1

double ROUNDUP( double number, int digits )
  {
     return Math.Ceiling(number * Math.Pow(10, digits)) / Math.Pow(10, digits);
  }

- msergeant

---

这个解决方案可以很好地处理正常情况。不幸的是，它不能正确处理负数，也不能正确处理负数位数（Excel支持），当“数字”接近像Double.Max这样的大值时，它也不能正确工作，因为你正在乘以一个非常大的值，它会超出范围。 - Liviu Trifoi

Answer 2

这是我尝试编写的类似于 Excel ROUNDUP 函数的解决方案。我尝试覆盖了负小数、负数位数（是的，Excel 支持这种情况）和大的小数值等情况。

public static decimal RoundUp(decimal number, int digits)
{
    if (digits > 0) 
    {
        // numbers will have a format like +/-1.23, where the fractional part is optional if numbers are integral
        // Excel RoundUp rounds negative numbers as if they were positive. 
        // To simulate this behavior we will use the absolute value of the number
        // E.g. |1.23| = |-1.23| = 1.23
        var absNumber = Math.Abs(number);

        // Now take the integral part (E.g. for 1.23 is 1)
        var absNumberIntegralPart = Decimal.Floor(absNumber);

        // Now take the fractional part (E.g. for 1.23 is 0.23)
        var fraction = (absNumber - absNumberIntegralPart);

        // Multiply fractional part by the 10 ^ number of digits we're rounding to
        // E.g. For 1.23 with rounded to 1 digit it will be 0.23 * 10^1 = 2.3
        // Then we round that value UP using Decimal.Ceiling and we transform it back to a fractional part by dividing it by 10^number of digits
        // E.g. Decimal.Ceiling(0.23 * 10) / 10 = Decimal.Ceiling(2.3) / 10 = 3 / 10 = 0.3
        var tenPower = (decimal)Math.Pow(10, digits);
        var fractionRoundedUp = Decimal.Ceiling(fraction * tenPower) / tenPower;

        // Now we add up the absolute part with the rounded up fractional part and we put back the negative sign if needed
        // E.g. 1 + 0.3 = 1.3
        return Math.Sign(number) * (absNumberIntegralPart + fractionRoundedUp);
    } else if (digits == 0)
    {
        return Math.Sign(number) * Decimal.Ceiling(Math.Abs(number));
    } else if (digits < 0) 
    {
        // negative digit rounding means that for RoundUp(149.12, -2) we will discard the fractional part, shift the decimal point on the left part 2 places before rounding up
        // then replace all digits on the right of the decimal point with zeroes
        // E.g. RoundUp(149.12, -2). Shift decimal point 2 places => 1.49. Now roundup(1.49) = 2 and we put 00 instead of 49 => 200

        var absNumber = Math.Abs(number);
        var absNumberIntegralPart = Decimal.Floor(absNumber);
        var tenPower = (decimal)Math.Pow(10, -digits);
        var absNumberIntegraPartRoundedUp = Decimal.Ceiling(absNumberIntegralPart / tenPower) * tenPower;
        return Math.Sign(number)*absNumberIntegraPartRoundedUp;
    }

    return number;
}

        [TestMethod]
        public void Can_RoundUp_Correctly()
        {
            Assert.AreEqual(1.466m, MathExtensions.RoundUp(1.4655m, 3));
            Assert.AreEqual(-1.466m, MathExtensions.RoundUp(-1.4655m, 3));
            Assert.AreEqual(150m, MathExtensions.RoundUp(149.001m, 0));
            Assert.AreEqual(-150m, MathExtensions.RoundUp(-149.001m, 0));
            Assert.AreEqual(149.2m, MathExtensions.RoundUp(149.12m, 1));
            Assert.AreEqual(149.12m, MathExtensions.RoundUp(149.12m, 2));
            Assert.AreEqual(1232m, MathExtensions.RoundUp(1232, 2));
            Assert.AreEqual(200m, MathExtensions.RoundUp(149.123m, -2));
            Assert.AreEqual(-200m, MathExtensions.RoundUp(-149.123m, -2));
            Assert.AreEqual(-20m, MathExtensions.RoundUp(-12.4655m, -1));
            Assert.AreEqual(1.67m, MathExtensions.RoundUp(1.666666666666666666666666666m, 2));
            Assert.AreEqual(1000000000000000000000000000m, MathExtensions.RoundUp(999999999999999999999999999m, -2));
            Assert.AreEqual(10000000000000m, MathExtensions.RoundUp(9999999999999.999m, 2));
        }

- Liviu Trifoi

Answer 3

Math.Ceiling 是你要找的。

- MiffTheFox

---

请注意，在执行 Math.Ceiling 函数之前，OP 需要先乘以 100，然后再除以 100，因为此函数无法指定小数位数。 - Michael Bray

Math.Ceiling(474.87212666666666666666666666667) 返回的是 475，这不是我想要的。 - nandin

Math.Ceiling(474.87212666666666666666666666667*100)/100 可以正常运行。谢谢你们。 - nandin

Answer 4

这里是ROUNDUP和ROUNDDOWN的正确计算方法：

private static object RoundDown(List<Expression> p)
{
    var target = (decimal)p[0].Evaluate();
    var digits = (decimal)p[1].Evaluate();

    if (target < 0) return (Math.Ceiling((double)target * Math.Pow(10, (int)digits)) / Math.Pow(10, (int)digits));

    return Math.Floor((double)target * Math.Pow(10, (int)digits)) / Math.Pow(10, (int)digits);
}

private static object RoundUp(List<Expression> p)
{
    var target = (decimal)p[0].Evaluate();
    var digits = (decimal)p[1].Evaluate();

    if (target < 0) return (Math.Floor((double)target * Math.Pow(10, (int)digits)) / Math.Pow(10, (int)digits));
    return Math.Ceiling((double)target * Math.Pow(10, (int)digits)) / Math.Pow(10, (int)digits);
}

- ladeangel