How to use the 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.