How to use the Excel MOD function

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:

Excel MOD - How to use the Excel MOD function

Syntax:  =MOD(A2,B2)

Result:

Excel MOD function - How to use the Excel MOD function

Based on the Excel spreadsheet above, the following MOD examples would return:

Syntax: =MOD(A3,B3)
Result: 1

Syntax: =MOD(A4,B4)
Result: -2

Syntax: =MOD(A5,B5)
Result: 3

Syntax: =MOD(A6,B6)
Result: 2

Syntax: =MOD(A7,B7)
Result: 0

Syntax: =MOD(A8,B8)
Result: 10

Syntax: =MOD(A9,B9)
Result: 3

Syntax: =MOD(A10,B10)
Result: 1

Syntax: =MOD(A11,B11)
Result: -1

Syntax: =MOD(A12,B12)
Result: -2

Note:

  1. The result of the MOD function has the same sign as the divisor.
  2. If the divisor is 0, MOD returns the #DIV/0! error because you cannot divide by zero.
  3. If the number or divisor is a text value, an Excel Mod formula returns the #VALUE! error.

Practical examples

Specify a number in every Nth cell. Suppose we are preparing a list of expenses of every month as below data:

READ:  How to use the Excel AGGREGATE function

MOD function - How to use the Excel MOD function

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.

Syntax: =IF(MOD((ROW()-1),3)=0,559,””)

Result:

MOD function in excel - How to use the Excel MOD function

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.

Add a Comment

Your email address will not be published. Required fields are marked *