How does modulus differ in C# vs. Excel?

Question

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

See ouah's answer [here](http://stackoverflow.com/questions/11720656/modulo-operation-with-negative-numbers) - remainder vs modulo. As per [the doc](https://msdn.microsoft.com/en-us/library/0w4e0fzs.aspx) % in C# is a remainder operator, not a modulo operator. — fvu, Feb 14 '16 at 16:03
@fvu: Though that is plausible, you have not actually put your finger on the germane difference. See my answer. — Eric Lippert, Feb 14 '16 at 16:18

score 14 · Answer 1 · answered Feb 14 '16 at 16:16

14

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.

answered Feb 14 '16 at 16:16

Eric Lippert

647,829
179
1,238
2,067

Is there a way to control the mode .NET calculates the modulo or is it a manual hack? – BanksySan Mar 11 '17 at 17:06
2

@BanksySan: C# does not compute the modulus; it computes the remainder according to the rule listed above. If you want to compute some other quantity, then you can easily do so. For example, in C# the modulus of x with respect to positive y is `((x % y) + y) % y)` even if x is negative. – Eric Lippert Mar 11 '17 at 20:36
OK, gotcha. Thanks. – BanksySan Mar 11 '17 at 20:41

score 6 · Accepted Answer · answered Feb 14 '16 at 16:19

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

How does modulus differ in C# vs. Excel?

2 Answers2

Linked