MOD Function – Explained with Examples

The MOD function is Excel’s modulo math function which returns the remainder after the nearest lower value multiple of a supplied divisor are subtracted.

Function Syntax

=MOD(number, divisor)

Usage

The MOD function works by removing whole number multiples of the divisor from the number, until you have a value less than the devisor left. This value is the output of the MOD function.

  1. The sign of the output is the same as for the divisor
  2. A divisor of zero returns a #DIV/0! (divide by zero) error
  3. The multiple subtracted from the number is the highest divisor‘s multiple lower than the number
    1. For negative numbers, the divisor‘s multiple is the next nearest multiple ‘more negative‘ than the negative number
  4. The MOD function can be written in terms of an INT function
    1. MOD(number, divisor) = number – divisor*INT(number/divisor)

Examples

MOD with positive numbers

We will start by considering a number which is is a multiple of the divisor:

MOD(6,3) = 0

3 divides into 6 perfectly with no remaining whole number so we get 0.

Next, consider a number is not a multiple of the divisor:

MOD(8,3) = 2

3 divides into 8 twice, with 2 left over. The MOD function returns 2.

MOD with numbers smaller than the divisor

Where the number is smaller than the divisor, the result is just the number: 

MOD(2,3) = 2

MOD function with negative numbers

However the number is negative, MOD works slightly differently.

Firstly, the output of MOD (for both positive and negative numbers) preserves the sign in the output of the divisor.

Using the example above, but by changing the number 8 to -8, we get a different answer:

MOD(-8,3) = 1

As before the MOD function returns the value remaining after multiples of the divisor have been removed.

NOTE: The multiple of the divisor removed from the number to leave a remainder, must be the multiple which is equal to or lower (or more negative) than the input number.

So for a divisor of 3, its first multiple lower than our number -8, is -9. (similar to our original example where the first multiple lower than +8 was +6).

The difference left over is between -9 and -8 is 1, that is “(-8) – (-9) = 1”. Therefore MOD(-8, 3) returns 1

Related Topics

F004