MOD and INT in Excel: Remainders and Whole Numbers
By ExcelReady TeamUpdated 6 min read
The short answer
MOD returns the remainder after division: =MOD(127,12) gives 7, because 12 goes into 127 ten times with 7 left over. INT rounds a number down to a whole number: =INT(127/12) gives 10. Use them together to split a quantity into full groups and what's left, such as boxes and spare items, or hours and minutes.
Key takeaways
- =MOD(number, divisor) returns the remainder; =INT(number) rounds down to the nearest whole number.
- INT(total/size) counts full groups and MOD(total, size) counts what's left over.
- MOD(A2,2)=0 tests for an even number; MOD(ROW(),3)=0 marks every third row.
- INT always rounds down, so INT(-2.5) is -3; TRUNC(-2.5) cuts the decimals and gives -2.
- MOD with a divisor of 0 returns #DIV/0!.
On this page+
The Excel MOD function returns the remainder after division, and the INT function rounds a number down to a whole number. Together they answer questions like "how many full boxes, and how many items left over?" or "how many hours and minutes is 135 minutes?". This guide is for beginners and shows both functions on small, practical examples, plus the edge cases with negative numbers and zero.
What do MOD and INT do?#
MOD gives the remainder, and INT gives the whole-number part rounded down.
=MOD(number, divisor)
=INT(number)| Formula | Result | Why |
|---|---|---|
=MOD(127,12) | 7 | 12 × 10 = 120, and 127 − 120 = 7 left over |
=INT(127/12) | 10 | 127 ÷ 12 = 10.58..., rounded down to 10 |
=MOD(10,2) | 0 | 10 divides by 2 exactly, so nothing is left |
=INT(7.8) | 7 | INT always rounds down |
"Divisor" means the number you divide by. Both functions work in every version of Excel on Windows, Mac and the web.
How to use INT and MOD to split items into boxes#
The most common job for this pair is splitting a quantity into full groups and the remainder.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Items | Per box | Full boxes | Left over |
| 2 | ORD-1 | 127 | 12 | 10 | 7 |
| 3 | ORD-2 | 60 | 12 | 5 | 0 |
| 4 | ORD-3 | 45 | 8 | 5 | 5 |
Step 1: Count the full boxes with INT#
In D2, type:
=INT(B2/C2)127 ÷ 12 is 10.58, and INT drops the .58, so the result is 10 full boxes.
Step 2: Count the leftover items with MOD#
In E2, type:
=MOD(B2,C2)The result is 7: the items that don't fill a box. Check it: 10 boxes × 12 + 7 = 127. Fill both formulas down with Ctrl+D (Windows) or ⌘D (Mac).
Step 3: Count the boxes you need in total#
If the leftovers still need a box of their own, you want 11 boxes, not 10. That is a "round up" job: =ROUNDUP(B2/C2,0) returns 11. Our guide to ROUND, ROUNDUP and ROUNDDOWN explains when to use each.
How to convert minutes to hours and minutes#
INT and MOD turn a total into two units, such as minutes into hours and minutes. With 135 minutes in A2:
=INT(A2/60) → 2 (full hours)
=MOD(A2,60) → 15 (minutes left over)To show both in one cell, join them with &:
=INT(A2/60)&"h "&MOD(A2,60)&"m"The result is the text 2h 15m. The same pattern converts days into weeks and days (/7 and ,7), or months into years and months (/12 and ,12).
How to use MOD for even numbers and every nth row#
MOD is the standard way to test for "every 2nd", "every 3rd" or "every nth" item. If the remainder is 0, the number divides exactly.
- Even or odd:
=MOD(A2,2)=0returns TRUE for even numbers. (=ISEVEN(A2)does the same.) - Every third row:
=MOD(ROW(),3)=0returns TRUE on rows 3, 6, 9 and so on. ROW() returns the current row number. - Inside IF:
=IF(MOD(A2,2)=0,"Even","Odd")labels each number.
A popular use is banded rows: in Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =MOD(ROW(),2)=0 and pick a fill colour to shade every other row. You can also combine MOD with SUMPRODUCT to add only every nth row, for example =SUMPRODUCT((MOD(ROW(B2:B13),3)=0)*B2:B13).
How INT and MOD handle decimals and dates#
INT keeps the whole part, and MOD(number,1) keeps only the decimal part.
=INT(7.8)returns 7, and=MOD(7.8,1)returns 0.8.- Excel stores a date and time as one number: the whole part is the date, and the decimal is the time of day. So
=INT(A2)gives just the date, and=MOD(A2,1)gives just the time. Format the results as a date or time to read them.
Floating-point note: =MOD(7.8,1) may show as 0.7999999999999998 if you widen the decimals, because computers store some decimals inexactly. Wrap it in ROUND if you compare it with another value: =ROUND(MOD(A2,1),2).
Negative numbers, zero and other edge cases#
Negative numbers are where INT and MOD surprise people.
| Formula | Result | Why |
|---|---|---|
=INT(-2.5) | -3 | INT rounds down, toward the more negative number |
=TRUNC(-2.5) | -2 | TRUNC just removes the decimals |
=MOD(-7,3) | 2 | In Excel, the result takes the sign of the divisor |
=MOD(7,-3) | -2 | Negative divisor, so a negative result |
=MOD(10,0) | #DIV/0! | You can't divide by zero |
If your data might contain negative amounts and you only want to drop the decimals, use TRUNC instead of INT. If the divisor comes from a cell that might be empty, guard the formula: =IF(C2=0,"",MOD(B2,C2)).
Excel also has QUOTIENT, which returns the whole-number part of a division (=QUOTIENT(127,12) is 10). For positive numbers it matches INT(127/12); many people use INT because it's shorter to explain.
MOD and INT vs the ROUND functions#
INT is one of several ways to remove decimals, and it's easy to mix them up.
| Function | 7.8 becomes | −7.8 becomes | Use it for |
|---|---|---|---|
INT | 7 | −8 | Full groups, whole days, always down |
TRUNC | 7 | −7 | Cut decimals without rounding |
ROUND(x,0) | 8 | −8 | Nearest whole number |
ROUNDUP(x,0) | 8 | −8 | Away from zero, e.g. boxes needed |
ROUNDDOWN(x,0) | 7 | −7 | Toward zero |
For money, prices and reports, ROUND is usually what you want; see the Excel ROUND functions guide and the ROUND function reference. Use INT and MOD when you are counting things that come in groups. Finance tests often mix these, for example whole months of interest plus leftover days, which is why they appear in the Excel skills for finance jobs.
Next, try a running total in Excel, another small formula that comes up constantly in reports.
Practise it#
Try the live exercise on this page to split an order into boxes and leftovers. Then practise ROUND, ROUNDUP and ROUNDDOWN in a live spreadsheet, where you'll work out pack counts and see how each rounding function changes the answer.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Order ORD-1 has 127 items (B2), packed 12 per box (C2). In E2, use MOD to find how many items are left over after filling the full boxes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Left over | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 |
Tip: MOD takes the number first (B2), then what you divide by (C2).
Practise this in a lesson
Frequently asked questions
What does the MOD function do in Excel?
MOD returns the remainder after one number is divided by another. =MOD(10,3) returns 1, because 3 goes into 10 three times with 1 left over.
What is the difference between INT and ROUND?
INT always rounds down to a whole number, so INT(7.8) is 7. ROUND goes to the nearest value, so ROUND(7.8,0) is 8.
How do I check if a number is even in Excel?
Use =MOD(A2,2)=0. It returns TRUE for even numbers and FALSE for odd numbers. ISEVEN(A2) does the same job.
Why does MOD return #DIV/0!?
The divisor is 0 or an empty cell. Division by zero has no remainder, so Excel returns #DIV/0!.
What is the difference between INT and TRUNC?
They agree for positive numbers. For negative numbers INT rounds down (INT(-2.5) is -3) while TRUNC just removes the decimals (TRUNC(-2.5) is -2).