FormuladrillMOD
MOD(number, divisor) returns the remainder after dividing number by divisor: MOD(10, 3) is 1. The result takes the sign of the divisor, so MOD(-10, 3) is 2. It is used to test odd or even, pick every nth row, split hours into days, and keep the decimal part of a number.
Syntax
MOD(number, divisor)| Part | What it is |
|---|---|
number | The value to divide. |
divisor | What to divide by. 0 gives #DIV/0!. |
Examples
=MOD(10, 3)Result: 1
=MOD(-10, 3)Result: 2
The sign follows the divisor: -10 = -4*3 + 2.
=IF(MOD(A2, 2) = 0, "Even", "Odd")Result: Even for 8, Odd for 7
=MOD(17.5, 1)Result: 0.5
MOD(x, 1) returns the decimal part; MOD(time, 1) returns the time part of a date-time.
Common mistakes
- #DIV/0!: the divisor is 0 or an empty cell.
- Expecting MOD(-10, 3) to be -1 as in some programming languages; Excel returns 2 because the sign follows the divisor.
- #NUM! on very large numbers: when number divided by divisor exceeds about 1.3E+17, use number - divisor*INT(number/divisor).
- Floating point: MOD(0.3, 0.1) may not be exactly 0. Round first: MOD(ROUND(A2, 2), 0.1).
Practice it
The drills on Formuladrill check your answer on live data, free, no account needed.
Start practicingQuestions
How do I shade every other row with MOD?
Conditional formatting with the formula =MOD(ROW(), 2) = 0 shades even rows. Use =MOD(ROW(), 3) = 0 for every third row.
How do I convert minutes into hours and minutes?
Hours: =INT(A2/60). Remaining minutes: =MOD(A2, 60). For 135 minutes that is 2 and 15.
Related
- ROUNDHow to round in Excel with ROUND, ROUNDUP, ROUNDDOWN and MROUND: to 2 decimals, to the nearest hundred and nearest 5, and why formatting is not rounding.
- ABSHow the Excel ABS function returns a number without its sign, with examples for the gap between two cells, a tolerance check and making negatives positive.
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.
- TODAY and NOWHow the Excel TODAY and NOW functions insert the current date or date and time, with formulas for days until a deadline, age, overdue flags and fixed dates.