MOD Function in Excel: Explained

In this article, you will learn how to use the MOD function in Excel.

‍

What does the MOD function in Excel do?

The MOD function in Excel is used to return the remainder when one number (dividend) is divided by another number (divisor). It calculates the modulo operation, which is the mathematical operation that determines the remainder after division.

‍

When is the MOD function Useful?

The MOD function is commonly used in various scenarios, such as:

1. Checking for divisibility: If the result of the MOD function is zero, it indicates that the dividend is evenly divisible by the divisor.
2. Grouping or categorizing data: The MOD function can be used to create groups or categories based on the remainder of a division operation.
3. Generating repeating patterns or cycles: By using the MOD function with a sequence of numbers, you can create repeating patterns or cycles based on the remainder values.
4. Handling Circular References: In certain iterative calculations or circular references, the MOD function can help control the iterations or determine convergence by using the remainder as a condition for stopping the calculation.

These are just a few examples of the applications of the MOD function. Its ability to calculate remainders is versatile and can be used in various scenarios where you must divide, categorize, or manipulate data based on the remainder of the division operations.

‍

How to use the MOD function in Excel?

The syntax of the MOD function is as follows:

=MOD(number, divisor)

number: This is the dividend, or the number you want to divide.

divisor: This is the divisor, or the number you want to divide by.

The MOD function returns the remainder as the result. The remainder will always be a non-negative number with the same sign as the divisor.

‍

Here are a few examples to illustrate the usage of the MOD function:

‍

Using MOD with positive numbers:

Calculate the remainder of dividing 10 by 3:

=MOD(10, 3)

Result: 1

This calculation divides the number 10 with the divisor 3, and returns the remainder, which is 1.

‍

Using MOD with negative numbers:

=MOD(-10, 3)

Result: 2

Here, the MOD function divides the number -10 by the divisor 3, and returns a remainder of 2. The output will always take the sign of the divisor. If this calculation were re-written to divide the number 10 with a divisor of -3, the output would be -2.

Note that when we divide -10 by 3, the remainder should be -1. However, Excel returns -2 due to a rounding issue that occurs because of the way the function has been constructed.

Essentially, the MOD function can also be written as:

MOD(number, divisor) = number - divisor*INT(number/divisor)

However, the INT function only rounds down, so when we have a negative being rounded with INT for MOD we move farther from zero. Thus in this case we get -2 instead of the expected -1.

‍

Using MOD to determine if a number is even or odd:

=MOD(F3, 2)

Result: 0 if the number in cell A1 is even, 1 if it's odd

‍

The MOD function is a useful tool for performing calculations involving remainders or cyclical patterns in Excel. It can be combined with other functions and formulas to solve specific problems or perform complex calculations.