Exceljet

Quick, clean, and to the point

Excel MOD Function

Excel MOD function
Summary 

The Excel MOD function returns the remainder of two numbers after division.  For example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.

Purpose 
Get the remainder from division
Return value 
The remainder
Syntax 
=MOD (number, divisor)
Arguments 
  • number - The number to be divided.
  • divisor - The number to divide with.
Usage notes 

Use the MOD function to get a reminder after division. For Example, MOD(3,2) returns 1, because 2 goes into 3 once, with a remainder of 1.

Examples

Here are a few examples of the MOD function with hardcoded values:

=MOD(12,3) // returns 0
=MOD(12,5) // returns 2
=MOD(100,33) // returns 1
=MOD(6.25,1) // returns 0.25

Alternative formula

With very large numbers, you may see the MOD function return a #NUM error. In that case, you can try an alternative version based on the INT function:

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

Notes

  • MOD is often seen in formulas that deal with "every nth" value
  • MOD is useful for extracting the time from a date
  • MOD always returns a result in the same sign as the divisor.
  • MOD will return a #DIV/0! error if the divisor is zero

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.