MOD Function in Excel
The MOD function returns the remainder from division. Perfect for determining even/odd numbers, creating patterns, and cycling sequences.
Excel
Google SheetsExcelGoogle Sheets=MOD(number, divisor)Quick Answer
MOD function MOD function is a mathematical function in Excel and Google Sheets that returns the remainder after dividing one number by another. It uses the syntax `=MOD(number, divisor)` where `number` is the value you're dividing, and `divisor` is what you're dividing by. For example, `=MOD(10, 3)` returns 1 because 10 divided by 3 is 3 with a remainder of 1.
=MOD(number, divisor)Practical Examples
Check if Number is Even or Odd
Determine whether employee IDs are even or odd for team assignments
Alternate Row Colors for Readability
Create zebra striping pattern in a large data table
Cycle Through Team Assignments
Distribute 50 projects evenly across 5 project managers
Convert 24-Hour to 12-Hour Time
Display time in 12-hour format for reports
Group Data into Buckets
Divide 1,000 customers into 10 equal analysis groups
Create Repeating Pattern Colors
Apply a 3-color pattern across product categories
Find Nth Occurrence with Remainder
Determine which week of month a date falls in
Common Errors and Solutions
Division by zero error in MOD function
The divisor parameter is zero or a cell reference that contains zero
1. Check that your divisor is not zero 2. Use IFERROR to provide a default value 3. Add validation to prevent zero divisors 4. Use an IF statement: =IF(B2=0, "Error", MOD(A2, B2))
Always validate divisor values before using MOD. For dynamic divisors, add a check: =IF(B2<>0, MOD(A2, B2), "Invalid divisor")
Example:
Invalid data type in MOD parameters
One or both parameters contain text instead of numbers, dates formatted as text, or other non-numeric values
1. Convert text numbers using VALUE() function 2. Check for hidden spaces with TRIM() 3. Verify cell formatting is set to Number 4. Use =MOD(VALUE(A2), VALUE(B2)) if cells contain text numbers 5. Remove any special characters or currency symbols
Use data validation to restrict cells to numeric input only. Check source data for text-formatted numbers by using ISNUMBER() function
Example:
Result is too large or calculation error
Extremely large numbers that exceed Excel's calculation limits (beyond 1E+308), or attempting to use MOD with values outside Excel's numeric range
1. Check if input numbers are within Excel's range (-1E+308 to 1E+308) 2. Break down large calculations into smaller steps 3. Consider if you need scientific notation for display 4. Verify formulas feeding into MOD aren't returning errors
Keep numbers within reasonable ranges. For very large number operations, consider using logarithmic transformations or alternative calculation methods
Example:
Invalid cell reference in MOD formula
Referenced cells have been deleted, moved, or the worksheet containing referenced cells has been removed. This often happens when copying formulas from other workbooks
1. Check if all referenced cells exist 2. Update cell references to point to correct locations 3. Recreate the formula with current cell references 4. If copying from another workbook, ensure source data is available
Use named ranges instead of direct cell references for more stable formulas. When deleting rows/columns, check if any MOD formulas reference them
Example:
MOD returns negative numbers when you expected positive
MOD's result takes the sign of the divisor. If divisor is negative, result will be negative. This is mathematically correct but can be unexpected
1. Use ABS() to ensure positive divisor: =MOD(A2, ABS(B2)) 2. Or wrap entire result: =ABS(MOD(A2, B2)) 3. Use positive divisors unless you specifically need negative remainders 4. Understand that MOD(-10, 3) returns 2, not -1
Always use positive divisors for predictable positive remainders. If working with potentially negative numbers, apply ABS() to the divisor
Example:
Best Practices and Advanced Techniques
Use MOD for Conditional Formatting Rules
MOD excels in conditional formatting for creating patterns. Use =MOD(ROW(), 2)=0 to highlight every other row, or =MOD(ROW(), 3)=0 for every third row. This creates professional-looking zebra stripes that automatically extend as you add data.
Combine with IF for Readable Results
Raw MOD results (0, 1, 2, etc.) aren't always intuitive. Wrap MOD in IF to create meaningful labels. For even/odd: =IF(MOD(A1,2)=0,"Even","Odd"). For quarters: =IF(MOD(MONTH(A1),3)=1,"Start",IF(MOD(MONTH(A1),3)=0,"End","Mid")).
Remember the -1, +1 Pattern for Natural Numbering
When cycling through items numbered 1-N (not 0 to N-1), use MOD(value-1, N)+1. This ensures your first item is 1, not 0. For example, assigning 100 tasks to 5 people (numbered 1-5): =MOD(A2-1, 5)+1.
Be Careful with Negative Numbers
MOD with negative dividends can produce unexpected results. MOD(-7, 3) returns 2, not -1, because the result takes the sign of the divisor. If you need traditional remainder behavior, use: =number - divisor*INT(number/divisor).
Performance: MOD is Lightning Fast
MOD is one of Excel's fastest functions. Unlike complex array formulas or VLOOKUP on large datasets, MOD performs calculations almost instantly even with millions of rows. Use it freely for row-by-row calculations without performance concerns.
Use Named Ranges for Divisors
When using the same divisor across many formulas (like MOD(A2, 7) for weekdays), create a named range for the divisor. This makes formulas more readable and easier to update. Change 'WeekLength=7' in one place instead of editing dozens of formulas.
MOD Works with Decimals
MOD isn't just for integers—it handles decimals perfectly. =MOD(7.5, 2.3) returns 0.6. This is useful for scientific calculations, measurement conversions, or any scenario with fractional remainders.
Create Dynamic Grouping with MOD and CHOOSE
Combine MOD with CHOOSE for powerful dynamic categorization. =CHOOSE(MOD(A1-1,3)+1,"Low","Medium","High") cycles through categories automatically. This technique works perfectly for priority levels, risk ratings, or any repeating classification system.
Need Help with MOD Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
The ABS function returns the absolute value of a number, removing any negative sign. Learn syntax, examples, and common errors with this complete guide.
ExcelSheetsMaster the IF function with practical examples and error solutions. Learn conditional logic in Excel and Google Sheets for smarter decision-making.
ExcelSheetsThe INT function rounds numbers down to the nearest integer by removing decimals. Learn syntax, practical examples, and error solutions.
ExcelSheetsMaster the ROUND function to round numbers to specified decimal places with practical examples and error solutions for Excel and Google Sheets.
ExcelSheets