Skip to content

Excel function · Math

Excel MOD function

MOD returns the remainder after one number is divided by another: 125 items packed in boxes of 12 leave 5 over. Shift lengths across midnight and flagging every third record are built with it.

What it does

=MOD(125,12) returns 5: 12 goes into 125 ten times and 5 is left. For the whole part of the division use QUOTIENT or INT(125/12); together they are the basis of box, pallet and pack calculations.

The remainder takes the sign of the divisor: =MOD(-7,3) returns 2 and =MOD(7,-3) returns -2. That is useful with times: on a shift that passes midnight the end time looks smaller than the start, and =MOD(End-Start,1) makes up the negative difference to a full day and gives the right duration.

Sequence numbers make regular patterns easy: =MOD(A2,3)=0 catches every third record and =MOD(A2,2)=0 the even numbers. In conditional formatting, =MOD(ROW(),2)=0 shades alternate rows. ISEVEN and ISODD tell you directly whether a number is even or odd.

Syntax

English Excel=MOD(number, divisor)
Turkish Excel=MOD(sayı; bölen)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
numberRequiredThe number, cell or formula to divide.
divisorRequiredThe number to divide by. It cannot be 0; the result takes its sign.

Examples

1Units left over after packing boxes

QUOTIENT gives the number of full boxes and MOD the number of units left over.

D2fx=QUOTIENT(B2,C2)
Units left over after packing boxes: example table
ABCDE
1ProductQtyPer BoxFull BoxesLeft Over
2Grounded socket12512105
3Circuit breaker30025120
4Light switch981098
5Cable duct612031
  • D2 (filled down to D5)=QUOTIENT(B2,C2)
  • E2 (filled down to E5)=MOD(B2,C2)

125 sockets fill 10 boxes with 5 left over. The switches leave 8 and the cable ducts 1.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Length of a shift that passes midnight

On a night shift the end time is smaller than the start, so a plain subtraction gives a negative result. MOD takes the difference relative to 1 (one day) and makes up the negative result; day shifts are unaffected.

D2fx=MOD(C2-B2,1)
Length of a shift that passes midnight: example table
ABCD
1EmployeeStartEndDuration
2Emma08:0016:3008:30
3Liam22:0006:3008:30
4Olivia16:0000:3008:30
5Noah23:3007:1507:45
  • D2 (filled down to D5)=MOD(C2-B2,1)

Liam's 22:00-06:30 shift lasts 08:30; Noah's lasts 07:45. Emma's day shift comes out right too: 08:30.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Flag every third order for inspection

Quality control inspects one order in three. When the sequence number divides by 3 the remainder is 0, and the row is flagged.

C2fx=IF(MOD(A2,3)=0,"Inspect","-")
Flag every third order for inspection: example table
ABC
1SeqOrderInspect
21S-1001-
32S-1002-
43S-1003Inspect
54S-1004-
65S-1005-
76S-1006Inspect
  • C2 (filled down to C7)=IF(MOD(A2,3)=0,"Inspect","-")

Orders 3 and 6 show Inspect; the others stay at -. Use 5 instead of 3 to pick every fifth order.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#DIV/0!

Cause: divisor is 0 or an empty cell.

Fix: Check the divisor; for cells that may be empty, use =IF(B2=0,"",MOD(A2,B2)).

Comparison fails

Cause: With decimals, the remainder can be stored with a tiny difference even though it looks right; =MOD(5.3,1)=0.3 can return FALSE.

Fix: Round before comparing: =ROUND(MOD(5.3,1),2)=0.3.

Good to know

  • For the length of a shift that passes midnight, use =MOD(C2-B2,1) and give the result a time format.
  • To get the decimal part of a number, =MOD(A2,1) is enough: it returns 0.25 for 7.25.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a column that calculates the hours worked from start and end times, including shifts that pass midnight

Convert to table

Bring your shift list into the tool and type this. Nexell builds the duration column with real Excel formulas and shows it in the preview first; in the downloaded file, the duration follows any change in the times.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I find odd and even numbers with MOD?

=MOD(A2,2) returns 0 for even numbers and 1 for odd ones. For TRUE/FALSE directly, use ISEVEN or ISODD.

What is the difference between MOD and QUOTIENT?

QUOTIENT returns the whole part of a division and MOD the remainder. Dividing 125 by 12, QUOTIENT returns 10 and MOD 5; 10 × 12 + 5 = 125.

Related functions

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free