CODEWITHSUNDEEP
c#excel
willem
willem

Reputation: 27027

How does modulus differ in C# vs. Excel?

I'm experimenting with negative-base number systems, and I use Excel to play with and check my calculations.

I notice that there are differences in C# vs. Excel. Why does C# return a different result than Excel?

For example:

C#: 146 % -3 = 2

Excel: mod(146, -3) = -1

Upvotes: 5

Views: 1503

Answers (2)

Cody Gray
Cody Gray

Reputation: 244951

As the Wikipedia article says, a modulo operation is dividend % divisor == remainder. The problem comes when either of the operands are negative values. At that point, the naive mathematical definition breaks down and the result becomes implementation-dependent.

In Excel, the mod operator always returns a result with the same sign as the divisor. Mathematically, the quotient used in the modulo operation is rounded downwards (towards −∞). In pseudo-code:

quotient = floor(dividend / divisor)
mod      = dividend - (divisor * quotient)

Therefore, for 146 and -3:

quotient = -49      // floor(146 / -3)
mod      = -1       // 146 - (-3 * -49) == 146 - 147

In C#, it is the opposite: the result always has the same sign as the dividend. This is because the quotient is truncated toward 0. In pseudo-code:

quotient = truncate(dividend / divisor)
mod      = dividend - (divisor * quotient)

Therefore:

quotient = -48     // truncate(146 / -3)
mod      = 2       // 146 - (-3 * -48) == 146 - 144

Upvotes: 6

Eric Lippert
Eric Lippert

Reputation: 660445

Let's suppose we have four integers: x, y, q, and r such that

 q = x / y
 r = x - q * y

I hope that it makes sense that the quotient and remainder must have this relationship.

Now we come to the difference between C# and Excel. The difference is actually in the division, not the remainder. When computing the quotient of two integers, C# rounds towards zero, and Excel rounds down. That is, in C# 8 / -3 is -2, and in excel, INT(8 / -3) is -3.

From that fact you can deduce why the remainders are different.

Upvotes: 14

Related Questions