MOD Function (LibreOffice Calc)
The MOD function in LibreOffice Calc returns the remainder after division. It is essential for grouping, indexing, periodic patterns, and algorithmic logic. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the MOD Function Does ▾
- Returns the remainder after division
- Works with positive and negative numbers
- Useful for grouping, indexing, and periodic patterns
- Works with cell references, formulas, and expressions
- Integrates with FLOOR, CEILING, INT, and TRUNC
It is designed to be simple, powerful, and universally compatible.
Syntax ▾
MOD(number; divisor)
Arguments
-
number:
The value to divide. -
divisor:
The number to divide by. Must be non‑zero.
Basic Examples ▾
Simple remainder
=MOD(10; 3)
Returns 1.
MOD with a cell reference
=MOD(A1; 5)
Returns the remainder of A1 divided by 5.
Even/odd detection
=MOD(A1; 2)
Returns 0 for even numbers, 1 for odd numbers.
Cycle through values
=MOD(ROW(); 3)
Repeats 0,1,2,0,1,2,…
Advanced Examples ▾
Group values into buckets of 10
=MOD(A1; 10)
Returns the position of A1 within its 10‑value bucket.
Alternating row shading
=MOD(ROW(); 2)
Returns 0/1 pattern for zebra striping.
MOD with negative numbers
=MOD(-10; 3)
Returns 2 (Calc ensures a non‑negative remainder).
MOD for scheduling cycles
=MOD(A1; 7)
Useful for weekly rotation patterns.
MOD for indexing repeating sequences
=INDEX($A$1:$A$3; MOD(ROW()-1; 3)+1)
Cycles through A1, A2, A3 repeatedly.
MOD for time calculations
=MOD(A1; 1/24)
Returns the fractional remainder of time within an hour.
MOD with FLOOR to compute bucket start
=A1 - MOD(A1; 10)
Returns the lower bound of A1’s 10‑value bucket.
Common Errors and Fixes ▾
Err:532 — Division by zero
Occurs when:
- divisor is zero
- divisor is an empty cell
- divisor is text
Fix:
Ensure divisor is a non‑zero numeric value.
MOD returns unexpected results with negative numbers
Cause:
- Calc returns a non‑negative remainder, even for negative inputs.
Example:
MOD(-10; 3) → 2
MOD returns 0 unexpectedly
Possible causes:
- number is an exact multiple of divisor
- number is stored as text
- divisor is larger than number
Fix:
Convert text to numbers using:
Data → Text to Columns → OK
Best Practices ▾
- Use MOD for grouping, indexing, and periodic patterns
- Use MOD with ROW() or COLUMN() for formatting logic
- Use MOD with FLOOR to compute bucket boundaries
- Use MOD for cycle detection in scheduling and rotation systems
- Avoid using MOD with zero or text values
Related Patterns and Alternatives ▾
- Use FLOOR to compute the lower bound of a bucket
- Use CEILING to compute the upper bound
- Use INT for integer division
- Use TRUNC to remove decimals
- Use A1 - divisor * INT(A1/divisor) as a manual MOD alternative
By mastering MOD and its combinations with other math functions, you can build elegant, pattern‑driven models in LibreOffice Calc that handle grouping, indexing, and periodic logic with precision.