Returns the remainder after division. Essential for mathematical calculations, determining even/odd numbers, creating repeating patterns, and time calculations in Excel.
Master the fundamentals of Excel MOD function
The MOD function returns the remainder after a number is divided by a divisor. It's essential for mathematical operations like determining even/odd numbers, creating repeating patterns, time calculations, and cycling through values. MOD is particularly useful in formulas where you need to wrap values within a specific range.
Returns remainder, not quotient
Result has same sign as divisor
MOD(number, 2) detects even/odd
Creates repeating patterns
Function-specific parameters
Function-specific return type
Determine if numbers are even or odd
Create repeating sequences and cycles
Convert time units (hours, minutes, seconds)
Validate numbers within specific ranges
Exact matching required
Returns numeric position
Handles missing text gracefully
=MOD(number, divisor)The number for which you want to find the remainder
The number by which you want to divide number. Must not be zero.
The remainder after division (has same sign as divisor)
Description: Returns the remainder after a number is divided by a divisor
Get remainder from division
Returns 1 because 10 ÷ 3 = 3 remainder 1. MOD gives only the remainder portion.
Use MOD function in VBA to find remainders
' Basic MOD in VBA
Range("C1").Value = Application.WorksheetFunction.Mod(10, 3)
' Returns: 1
' Check if number is even
Sub CheckEvenOdd()
Dim num As Integer
num = Range("A1").Value
If Application.WorksheetFunction.Mod(num, 2) = 0 Then
Range("B1").Value = "Even"
Else
Range("B1").Value = "Odd"
End If
End Sub
' Create repeating pattern
Sub CreatePattern()
Dim i As Integer
For i = 1 To 20
Dim remainder As Integer
remainder = Application.WorksheetFunction.Mod(i - 1, 7) + 1
Cells(i, 1).Value = remainder
Next i
End Sub
' Time calculations
Sub ConvertSeconds()
Dim totalSeconds As Long
totalSeconds = Range("A1").Value
Dim hours As Long, minutes As Long, seconds As Long
hours = Int(totalSeconds / 3600)
minutes = Int(Application.WorksheetFunction.Mod(totalSeconds, 3600) / 60)
seconds = Application.WorksheetFunction.Mod(totalSeconds, 60)
Range("B1").Value = hours & "h " & minutes & "m " & seconds & "s"
End SubDetermine if numbers are even or odd
Create repeating sequences (e.g., day of week)
Convert seconds to hours, minutes, seconds
Keep values within a range (0 to n-1)
Conditional formatting based on row parity
Group items into fixed-size groups
MOD returns #DIV/0! when divisor is zero
=IF(B1=0, "Error", MOD(A1, B1))Solution: Always validate divisor is not zero before using MOD. Use IF: =IF(divisor=0, "Error", MOD(number, divisor)) or ensure divisor is never zero.
MOD returns unexpected negative values
=ABS(MOD(number, divisor)) for positive remainderSolution: Remember MOD result has the same sign as the divisor. MOD(-10, 3) = 2 (positive), MOD(10, -3) = -2 (negative). Use ABS if you need positive remainder.
MOD(number, 2) not detecting even/odd correctly
=IF(MOD(VALUE(A1), 2)=0, "Even", "Odd")Solution: Ensure number is actually numeric. Check for text values, spaces, or formatting issues. Use VALUE() to convert text to number: MOD(VALUE(A1), 2).
Repeating pattern formula not working
=MOD(ROW()-startRow, cycle)+1Solution: Verify MOD range matches desired cycle. MOD(ROW()-1, 7) cycles 0-6. Add +1 to cycle 1-7. Check starting row offset is correct.
MOD with decimal divisors returns unexpected results
=MOD(ROUND(A1, 10), ROUND(B1, 10))Solution: MOD works with decimals but can have precision issues. Use ROUND for decimal divisors: MOD(ROUND(number, 10), ROUND(divisor, 10)).