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.
Syntax: =MOD (number, divisor)
The MOD function syntax has the following arguments:
- Number Required. The number for which you want to find the remainder.
Divisor Required. The number by which you want to divide number. If the divisor is 0, then it will return the #DIV/0! error.
Example: Let’s look at some Excel MOD function examples and explore how to use the MOD function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following MOD examples would return:
- The result of the MOD function has the same sign as the divisor.
- If the divisor is 0, MOD returns the #DIV/0! error because you cannot divide by zero.
- If the number or divisor is a text value, an Excel Mod formula returns the #VALUE! error.
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.
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.