Updated August 9, 2023
MOD in Excel
MOD in Excel function is a mathematical function under Math and Trigonometry category, which is used to find the remainder number after dividing a dividend by any divisor number.
We all know when we divide the number 10 by 2, then we get 0 or nothing as the remainder, but when we do divide 10 by 3, then 1 is left. This is called the remainder, which we get when we use the Mod function and select both the number and divisor in the syntax.
MOD Formula
Below is the MOD Formula;
Where the supplied arguments are:
- Number – The number to be divided.
- Divisor – The number to divide by.
How to Use MOD Function in Excel?
MOD Function is very simple to use. Let us now see how to use the MOD function in Excel with the help of some examples.
Example #1
Let’s consider n=4, d=3. Apply the MOD Function
The Result will be:
The MOD function returns 1 because 3 goes into 4 once with a remainder of 1.
Example #2
Get the remainder from the division
Apply the MOD Function
The Answer will be:
Now drag & drop the formula for the rest values, and the result is:
The Summary for the above Example is as follows:
Example #3
The MOD function can also be used with a vector. Now we are taking a vector of integers and the divisor 3.
A = 1:5;
M = 3;
B = MOD(A, M)
The result will be:
1 2 0 1 2
Example #4
If a vector has some negative and positive values, the result will be positive if the divisor is positive.
A = [-4 -1 7 9];
M = 3;
B = MOD(A, M)
The result is:
2 2 1 0
Example #5
ADD only the even numbers in the given dataset:
For adding the even values in the above-given dataset, we use the below formula:
=SUMPRODUCT($A$13:$A$20*(MOD($A$13:$A$20,2)=0))
In the case of even numbers,(MOD($A$13:$A$20,2)=0) would return an array of TRUE, then the SUMPRODUCT function adds only those numbers returning TRUE.
Result is:
Example #6
ADD only the odd numbers in the given dataset:
For adding the odd values in the above-given dataset, we use the below formula:
=SUMPRODUCT($A$25:$A$31*(MOD($A$25:$A$31,2)=1))
In case of odd numbers, (MOD($A$25:$A$31,2)=1)would return an array of TRUE, then the SUMPRODUCT function adds only those numbers returning TRUE.
Result is:
So, this formula works by calculating the remainder using the MOD function.
Example #7
Specify a number in every Nth cell. Suppose we are preparing a list of expenses for every month as per below data:
The insurance premium is paid in every 3 months gap. Here we will use the MOD function to calculate the cell in every third row with the EMI value.
The formula is:
=IF(MOD((ROW()-1),3)=0,267,””)
The result is:
Drag & drop this formula for every value, and the result is:
Here, the ROW function gives the row number, and then we subtract 1. Now the MOD function checks the remainder when this value is divided by 3. For every 3rd row, the value will be 0. In this case, IF function would return 267, else will return a blank.
Where to use the MOD function:
- Where do we need to calculate ODD or EVEN numbers
- OR, where do we need to find out to calculate the cell
- Need to calculate the remainder
Things to Remember
- The result of the MOD function has the same sign as the divisor.
- If the divisor is 0, MOD returns an error value #DIV/0!
- The MOD function can also be represented in terms of the INT function:
MOD(n, d)=n-d*INT(n/d)
Where n=number, d=divisor
Recommended Articles
This has been a guide to the Excel MOD function. Here we discuss the MOD Formula and how to use the MOD function, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
