MOD Function (Table of Contents)
MOD in Excel
Mod function is 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 number 10 with 2 then we get 0 or nothing as remainder but when we do divide 10 by 3 then 1 is left this is called the remainder and this we get when we use Mod function and selecting both 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 above Example is as follows:
Example #3
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, then 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 case of even numbers,(MOD($A$13:$A$20,2)=0) would return an array of TRUE, then SUMPRODUCT function adds only those numbers that are 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 SUMPRODUCT function adds only those numbers that are returning TRUE.
Result is:
So, this formula is working 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 of every month as below data:
The insurance premium is paid in every 3 months gap. Here we will use 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 MOD function:
- Where we need to calculate ODD or EVEN numbers
- Where we need to find out to calculate the cell
- Need to calculate the remainder
Things to Remember
- The result of MOD function has the same sign as the divisor.
- If divisor is 0, MOD returns an error value #DIV/0!
- The MOD function can also be represented in terms of INT function as:
MOD(n, d)=n-d*INT(n/d)
Where n=number, d=divisor
Recommended Articles
This has been a guide to Excel MOD function. Here we discuss the MOD Formula and how to use MOD function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
