MOD and INT in Excel: how to use it
INT rounds a number down to a whole number, and MOD gives what is left after a division. Together they give a division with a remainder.
Try the first exercises for freeIn the lesson MOD and INT
What do MOD and INT do?
MOD gives what's left over when you divide a number: 12 goes into 100 eight times, and 4 is left over. INT rounds a number down to a whole number: it turns 8.33 into 8.
Together they give a division with a remainder, like the ones you did at school. You use them for full boxes and what's left over, to turn minutes into hours and minutes, or to see whether a number is even.
When do you use MOD and INT?
- How many full boxes of 12 you fill: =INT(A2/12).
- How many items are then left over: =MOD(A2,12).
- Minutes as hours and minutes: =INT(A2/60)&" hours and "&MOD(A2,60)&" minutes".
- Checking whether a number is even: =MOD(A2,2)=0.
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
What's left over after dividing.
Filled in: =MOD(A2,B2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
MOD | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | MOD |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
number | The number you divide. | A2 |
divisor | The number you divide by. | B2 |
, | A comma separates the arguments from each other. | , |
MOD and INT step by step
D1 contains =MOD(A2,B2). 100 items in boxes of 12: 8 full boxes, and 4 items are left over. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click D1 and type MOD with a bracket.
=MOD( - Click the number you're dividing, A2, and type a comma.
=MOD(A2, - Click the number you're dividing by, B2.
=MOD(A2,B2 - Close the bracket and press Enter: 12 goes into 100 eight times, and 4 are left over.
=MOD(A2,B2)
More examples
Full boxes with INT
D1 contains =INT(A2/B2). 100 divided by 12 is 8.33. INT keeps the whole part: 8 full boxes.
Minutes as hours and minutes
C1 contains =INT(A2/60)&" hours and "&MOD(A2,60)&" minutes". INT gives the full hours, MOD the minutes that are left over.
Even or odd
C1 contains =MOD(A2,2). The remainder when dividing by 2 is 1 for an odd number and 0 for an even number. ISEVEN tells you straight away with TRUE or FALSE.
INT: the whole part
=INT(A2/B2) gives 8: that's how many full boxes of 12 you fill with 100 items. INT always rounds down, even with 8.9.
Even or odd
A number is even if the remainder after dividing by 2 is zero: =MOD(A2,2)=0 gives TRUE for an even number.
Where it often goes wrong
- INT also rounds a negative number down: -8.3 becomes -9.
- Dividing by 0 gives #DIV/0!, with MOD too.
Practise this in the lesson MOD and INT. The first exercises are free.
Frequently asked questions
How do I turn minutes into hours and minutes?
With INT and MOD: =INT(A2/60)&" hours and "&MOD(A2,60)&" minutes" turns 135 into the text 2 hours and 15 minutes.
Read next
ROUNDCEILING and FLOORLARGE and SMALLMEDIAN and MODESEQUENCE and RANDBETWEEN