Syntax. MOD(number,divisor)
Definition. This function returns the remainder of a division operation. The result has the same sign as the divisor.
Arguments
number (required) The number for which you want to find the remainder
divisor (required) The number by which you want to divide number
Background. For certain calculations, you need the remainder of a division operation. This is also called modulo (mod). For this type of calculation, you can use the MOD() function.
If the divisor is 0, the MOD() function returns the #DIV/0! error.
The MOD() function can be expressed in terms of the INT() function:
=numerator-divisor • INT(numerator/divisor)
This formula is used for the modulo method in software systems. There are two types of modulo calculations, which return different results for negative arguments:
Math method (Excel MOD() function)
Z mod D = Z – D • [Z/D]
[Z/D] is the division in parentheses (for example, –4.3 ≈ –5) and corresponds to the result returned by the =INT(Z/D) function. For the math method, the following applies to negative values:
(•Z) mod D ≠ • (Z mod D)
Here is an example:
-7 mod 3 = 2 ≠ -1 = -(7 mod 3)
The result has the same sign as the divisor (D).
Symmetrical method
Z mod D = Z – D • (Z div D)
(Z div D) is the quotient rounded toward 0 (for example, –4.3 ≈ –4). For the symmetrical method, the following applies to negative values:
(• Z) mod D = • (Z mod D)
Here is an example:
-7 mod 3 = -1 = -1 = -(7 mod 3)
The result has the same sign as the numerator (Z).
Example. The MOD() function is often used together with other functions; for example, to add every second line (see Figure 16-20).
The formula is {=SUM(IF(MOD(ROW(C3:C8);2)=0;C3:C8;0))}. Because this
is an array formula, you have to press Ctrl+Shift+Enter after you enter the formula.
More examples for this function are:
=MOD(7,3)returns1.=MOD(-7,3)returns1(the value is positive because the divisor is positive).=MOD(7,-3)returns-1(the value is negative because the divisor is negative).=MOD(12.56,3.2)returns2.96.